Monday, March 28, 2011

T-SQL How to combine multiple rows into a single row over a group

The problem:
My Data looks like this.


But i want it to look like this:


How do i do it?

Like this...

SELECT TOP 100 Percent p1.grp,
          ( SELECT comment + ',' 
              FROM combine p2
             WHERE p2.grp = p1.grp
             ORDER BY comment
               FOR XML PATH('') , TYPE).value('.[1]', 'nvarchar(max)' ) AS AllComm
      FROM combine p1
     GROUP BY grp ;

Original source and other techniques found here