Transform multiple row values to 1 row values

Question

I have the following table

Id | dwCode| PRNo |
-----------------------
1    DW001    PR001
2    DW001    PR002

And I need the following result:

dwCode| PRNo |
-----------------------
DW001    PR001,PR002

Is there any short way to transform the data like this using MS SQL Server 2005?

Answer 1

You can use the FOR XML to do so:

SELECT 
  t1.dwCode,
  STUFF((
    SELECT ', ' + t2.PRNo
    FROM Table1 t2
    WHERE t2.dwCode   = t1.dwCode
    FOR XML PATH (''))
  ,1,2,'') AS PRNo
FROM Table1 t1
GROUP BY t1.dwCode;

See it in action here:

• SQL Fiddle Demo