SQL Tip: Concat entire column into a single CSV string

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/sql_tip_concat_entire_column_into_a_single_csv_string.htm]

Sometimes in SQL, you'll want to concat an entire column's values into a single string.

For example, say you have a parent-child relationship (like state-city, or entity-code), and in a parent's list page, you want to display a column with the CSV string of all the children.

State	City
IL	Chicago
IL	Springfield
IL	Rockford
WI	Madison

You can concatenate a list of values into a CSV string like "Chicago, Springfield, Rockford" by continually selecting into the same variable:

        declare @sCsv varchar(1000)
        set @sCsv = ''

        select @sCsv = @sCsv + City + ', '
        from MyTable
        where State = 'IL'; --any filter clause here

        --remove final ","
        if (Len(@sCsv) > 1)
                select @sCsv = substring(@sCsv,1,len(@sCsv)-1)
        return @sCsv