sql server - T-SQL: Combine rows to one row

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.

class="post-text" itemprop="text">

class="normal">Answer

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