T-SQL: Combine rows to one row

Question

I have the following table:

number  word
======  ====
1       AAA
2       BBB
2       CCCC
4       CCCC
4       BBB
4       AAA

Now I want to create a new table, where a "number" only occurs in one row. The corresponding values in "word" should be converted to comma sepeareted string.

The result table:

number  word
======  ====
1       AAA
2       BBB,CCCC
4       CCCCC,BBB,AAA

How can this solved with T-SQL? Thanks in advance.

http://codecorner.galanter.net/2009/06/25/t-sql-string-aggregate-in-sql-server/ — Yuriy Galanter, Jun 19 '15 at 18:02

score 2 · Accepted Answer · answered Jun 19 '15 at 18:18

2

I started so I may as well post mine too...

CREATE TABLE #test
(
    ID tinyint
    ,Word varchar(20)
);
INSERT INTO #test
VALUES
(1,'aaa')
,(1,'bbb')
,(2,'abc')
,(2,'def')
,(2,'ghi')
,(3,'zzz');

SELECT DISTINCT
a.ID
,STUFF((
    SELECT
        ',' + b.Word
    FROM #test b
    WHERE a.ID = b.ID 
    FOR XML PATH('')
),1,1,'') AS [Contains]
FROM #test a

answered Jun 19 '15 at 18:18

SQLGobbleDeGook

146
7

Thank you. This is exactly what I need! – uhu Jun 19 '15 at 18:26

score 0 · Answer 2 · answered Jun 19 '15 at 18:04

0

You need to use FOR XML Here is a demo of how it works SQL FIDDLE

answered Jun 19 '15 at 18:04

Alec.

5,371
5
34
69

2

Please do not post "link only" answers. Linked content can disappear. Post actual code. – Yuriy Galanter Jun 19 '15 at 18:06

T-SQL: Combine rows to one row

2 Answers2