SQL FOR XML 用法(根据条件,把多列合并在一起)
1.初始数据
2.合并数据
3.完整SQL
WITH V AS ( SELECT 'No-1' AS No, 'Name-1' AS Name, 'Remark-1' AS Remark UNION ALL SELECT 'No-1' AS No, 'Name-1' AS Name, 'Remark-2' AS Remark UNION ALL SELECT 'No-1' AS No, 'Name-1' AS Name, 'Remark-3' AS Remark UNION ALL SELECT 'No-1' AS No, 'Name-1' AS Name, 'Remark-4' AS Remark UNION ALL SELECT 'No-1' AS No, 'Name-2' AS Name, 'Remark-1' AS Remark UNION ALL SELECT 'No-1' AS No, 'Name-2' AS Name, 'Remark-2' AS Remark UNION ALL SELECT 'No-2' AS No, 'Name-2' AS Name, 'Remark-3' AS Remark UNION ALL SELECT 'No-2' AS No, 'Name-2' AS Name, 'Remark-4' AS Remark UNION ALL SELECT 'No-3' AS No, 'Name-3' AS Name, 'Remark-1' AS Remark UNION ALL SELECT 'No-3' AS No, 'Name-3' AS Name, 'Remark-2' AS Remark UNION ALL SELECT 'No-3' AS No, 'Name-3' AS Name, 'Remark-3' AS Remark ), V_GROUP AS ( SELECT NO, NAME FROM V GROUP BY NO, NAME ) --SELECT * FROM V --SELECT * FROM V_GROUP SELECT V_GROUP.* , ( SELECT ',' + Remark FROM V WHERE V.No = V_GROUP.No AND V.No = V_GROUP.No FOR XML PATH('')) AS REMARK FROM V_GROUP