Converting multiple rows into a single comma separated row

And here I come!
April 4, 2010
A better way of showing current year sales vs previous year
April 5, 2010
Show all

Converting multiple rows into a single comma separated row

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
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

6 Comments

  1. Anonymous says:

    How can I input this command it would be really helpful

  2. Jason Thomas says:

    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

  3. 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.

  4. Elias says:

    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!

  5. Hari says:

    replace the line
    from #country s )t
    with this one
    from #country t

    it should work then.

  6. […] 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 […]

Leave a Reply to Hari Cancel reply

Your email address will not be published. Required fields are marked *