求多行转列解决思路

求多行转列
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 行受影响)   */