id zhi
a 1
a 2
a 2
b 1
b 3
结果：
id zhi
a 1,2,2
b 1,3
记得以前做过，很久没做，忘记了。
------解决方案--------------------
- SQL code
WITH test (id, zhi) AS ( SELECT 'a', 1 UNION ALL SELECT 'a', 2 UNION ALL SELECT 'a', 2 UNION ALL SELECT 'b', 1 UNION ALL SELECT 'b', 3) select a.id, stuff((select ','+CONVERT(VARCHAR(5),zhi) from test b where b.id=a.id for xml path('')),1,1,'') 'zhi' from test a group by a.id /* id zhi ---- ---------------------------------------------------------------------------------------------------------------- a 1,2,2 b 1,3 (2 行受影响) */