 |
|||||||||
|
|||||||||
|
|
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
this is really interesting to me. However is it possible to do it for all
state. For instance:
Offhand I don't know a fast-eprformance way, but you could do something
like this:
select @sCsv = @sCsv + State + ': ' + GetAllCitites(State) + ', '
from (Select distinct State from MyTable) as T1
order by State
