本文深入探讨了SQL中FORXMLPATH函数的高级应用，通过实例展示了如何将多条记录的详细信息合并成简洁的字符串格式，特别关注于去除冗余的逗号，以及后续对合并后的数据进行进一步的筛选与提取操作。
1. 列出所有数据
2. 合并，带有多余的逗号
SELECT name,
(SELECT value+',' FROM test
WHERE name=A.name
FOR XML PATH('')) AS StuList
FROM test A
SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B
[1] 灵活运用 SQL SERVER FOR XML PATH. http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html
select * from test
2. 合并，带有多余的逗号
SELECT name,
(SELECT value+',' FROM test
WHERE name=A.name
FOR XML PATH('')) AS StuList
FROM test A
GROUP BY name
3. 最终效果
SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B
[1] 灵活运用 SQL SERVER FOR XML PATH. http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html
扫一扫
926
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
