for xml path 按分类合并行数据

SELECT B.Sonum,LEFT(Detail,LEN(Detail)-1) as itemnum FROM (
SELECT Sonum,
(SELECT ItemNum+',' FROM testtb 
  WHERE Sonum=A.Sonum 
  FOR XML PATH('')) AS Detail
FROM testtb A 
GROUP BY Sonum
) B