Saturday, February 22, 2014

Combine column from multiple rows into single row

he data looks like this:
id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy
My results need to look like this:
id  customer_code comments
------------------------------
1   Dilbert        Hard Worker
2   Wally          Lazy
DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;
Reference: http://dba.stackexchange.com/questions/17921/combine-column-from-multiple-rows-into-single-row