WITH t(Id,NAME) AS
(
SELECT 1, '张三,李四' UNION ALL
SELECT 1, '张三,王五' UNION ALL
SELECT 2, '李四,王五' UNION ALL
SELECT 2, '王五,赵六'
)
SELECT Id
,REPLACE(c,' ',',') AS NAME
FROM(
SELECT id,CONVERT(XML,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') AS NameXml
FROM (
SELECT Id,STUFF((SELECT ','+NAME FROM t AS b WHERE a.Id=B.Id FOR XML PATH('')),1,1,'') AS name
FROM t AS a
GROUP BY a.Id
) a
) B
CROSS APPLY (SELECT CAST(B.NameXml.query('distinct-values(root/v)') AS Nvarchar(MAX)) c) t
(
SELECT 1, '张三,李四' UNION ALL
SELECT 1, '张三,王五' UNION ALL
SELECT 2, '李四,王五' UNION ALL
SELECT 2, '王五,赵六'
)
SELECT Id
,REPLACE(c,' ',',') AS NAME
FROM(
SELECT id,CONVERT(XML,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') AS NameXml
FROM (
SELECT Id,STUFF((SELECT ','+NAME FROM t AS b WHERE a.Id=B.Id FOR XML PATH('')),1,1,'') AS name
FROM t AS a
GROUP BY a.Id
) a
) B
CROSS APPLY (SELECT CAST(B.NameXml.query('distinct-values(root/v)') AS Nvarchar(MAX)) c) t
B.NameXml.query('distinct-values(root/v)') --字段.query()有什么作用?
这里就是先把数据转化为XML然后用它的query()方法XML.query()....
query()方法对 xml 数据类型指定XQuery。XQuery是W3C的一种查询语言。