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
No comments:
Post a Comment