Recently, I have been bitten by the MSDN forums bug. I enjoy spending time in the SSAS and SSRS forums, and the learning that I take out from there is tremendous. Initially, I just used to read and learn, while now I reply to a lot of posts too (just the simple ones, keep the tough ones for the experts to solve 😉 ).
So the other day, there was this post – how can you convert multiple rows into a single comma separated row? For eg,
This can be done by the FOR XML command.
select DISTINCT State, (
select City + ‘,’ as [text()]
from tableA soi
where soi.State=t.State
order by City
for xml path( ” )
)
from tableA t
select City + ‘,’ as [text()]
from tableA soi
where soi.State=t.State
order by City
for xml path( ” )
)
from tableA t
A handy command, especially because you don’t have to use stored procedures to achieve the result.
UPDATE – 5/26/2016
I see that this post still gets a lot of hits, so just wanted to share the version that I use currently. The previous version works really slow with big tables, but this one works much faster.
SELECT OutTab.state ,
Cities =
STUFF ( ( SELECT ‘,’+InrTab.City
FROM tableA InrTab
WHERE InrTab.state= OutTab.state
ORDER BY InrTab.City
FOR XML PATH(”),TYPE
).value(‘.’,’VARCHAR(MAX)’)
, 1,1,SPACE(0))
FROM tableA OutTab
GROUP BY OutTab.State
Cities =
STUFF ( ( SELECT ‘,’+InrTab.City
FROM tableA InrTab
WHERE InrTab.state= OutTab.state
ORDER BY InrTab.City
FOR XML PATH(”),TYPE
).value(‘.’,’VARCHAR(MAX)’)
, 1,1,SPACE(0))
FROM tableA OutTab
GROUP BY OutTab.State
How can I input this command it would be really helpful
This is a normal SQL query and can be input anywhere where SQL query is accepted. If you are referring to SSRS, then this should be input in the dataset query
I found this blog when researching geom types in sql and tried your trick as well without success. I am using a table NLG_adm2 with geom and city/province data an have substituted name_1 for State and name_2 for City and am using nlg_adm2 instead of tableA. The statement becomes:
select name_1, (
select name_2 + ',' as [text()]
from nld_adm1 soi
where soi.name_1=t.name_1
order by name_2
for xml path( '' )
)
from nld_adm2 s)t
it does not execute and gives error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
It seems the "s)" should be removed.
It will execute then, but return duplicates for each province. Adding a "distinct" to the first select will fix the problem.
Also in your exemple you do not show the trailing "," that each line will have which will for cause problem depening on the use of the comma-fied string.
I tried this script and it doesnt work:
Here's the output:
select State, (
select City + ',' as [text()]
from #country soi
where soi.State=t.State
order by City
for xml path( '' )
)
from #country s )t
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
Created a table with those exact names.
Thank you!
replace the line
from #country s )t
with this one
from #country t
it should work then.
[…] since I wrote Converting multiple rows into a single comma separated row, I was trying to find a SQL command which will do the reverse, which is converting the single comma […]
Both the examples that are provided are nested select statements, Is there a way that can be moved to a join ? and bring the column from that joined table?
But this script throws duplicates in the comma seperated values. How we remove the duplicates?