t-sql 分组 合并字符串列值

id	name
1	a
1	b
1	c
2	d
2	e

parent children
1 a,b,c
2 d,e
3 f

WITH    t AS ( SELECT   1 parent ,
                        'a' child
               UNION
               SELECT   1 ,
                        'b'
               UNION
               SELECT   1 ,
                        'c'
               UNION
               SELECT   2 ,
                        'd'
               UNION
               SELECT   2 ,
                        'e'
               UNION
               SELECT   3 ,
                        'f'
             )
    SELECT  parent ,
            STUFF(( SELECT  ',' + child
                    FROM    t a
                    WHERE   b.parent = a.parent
                  FOR
                    XML PATH('')
                  ), 1, 1, '') children
    FROM    t b
    GROUP BY b.parent