SQL Server 2005 pivot table

Question

I want to pivot a column in SQL Server 2005. I'm pretty sure there is XML way to get it done but can't figure it out. Here is a table:

ID    Class
1     20002
1     20003
1     20004
2     20003
2     20012

Desired value is:

ID    Class
1     20002,20003,20004
2     20003,20012

Thanks in advance

Check out the PIVOT keyword that was introduced in SQL Server 2005. And [this question](http://stackoverflow.com/questions/198716/pivot-in-sql-2005) among many others. — DOK, Jan 30 '12 at 21:15
possible duplicate of [HOw to get CSV value for following scenario](http://stackoverflow.com/questions/4713476/how-to-get-csv-value-for-following-scenario) — Joe Stefanelli, Jan 30 '12 at 21:22
This is not `PIVOT`. There are many questions like this on SO, do a quick search with the tags sql-server and group-concat — Lamak, Jan 30 '12 at 21:27

score 3 · Accepted Answer · answered Jan 30 '12 at 21:16

I am not sure PIVOT (or UNPIVOT) are what you are looking for. Below is some code that I use when I need to get a CSV list embedded in a query. Hope it helps!

SELECT DISTINCT 
        ID
      , Class = STUFF(
                       cast(
                            (select ', ' + cast(Class as nvarchar) 
                             from TableName t2 
                             WHERE t2.ID = t1.ID 
                             for xml path('')) as nvarchar(2000))
                ,1,2, N'')
FROM TableName t1

SQL Server 2005 pivot table

1 Answers1