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

this question has answer here:

• how use group concatenate strings in sql server? 16 answers
• how create sql server function “join” multiple rows subquery single delimited field? [duplicate] 13 answers

i have following table:

number  word ======  ==== 1       aaa 2       bbb 2       cccc 4       cccc 4       bbb 4       aaa 

now want create new table, "number" occurs in 1 row. corresponding values in "word" should converted comma sepeareted string.

the result table:

number  word ======  ==== 1       aaa 2       bbb,cccc 4       ccccc,bbb,aaa 

how can solved t-sql? in advance.

i started may post mine too...

create table #test (     id tinyint     ,word varchar(20) ); insert #test values (1,'aaa') ,(1,'bbb') ,(2,'abc') ,(2,'def') ,(2,'ghi') ,(3,'zzz');  select distinct a.id ,stuff((     select         ',' + b.word     #test b     a.id = b.id      xml path('') ),1,1,'') [contains] #test