WITH a1 (msg) AS
(
SELECT '11,22,33,44' UNION ALL
SELECT '33,44,55,66'
)
,a2 AS
(
SELECT DISTINCT b.msg
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
SELECT msg=STUFF((SELECT ','+msg FROM a2 FOR XML PATH('')),1,1,'')
能不能帮忙优化一下比如现在结果是 11,22,33,33,44,44,55,66结果变成:11,22,33,44,55,66怎么做呢?
WITH a1 (msg) AS
(
SELECT '11,22,33,33,44,44,55,66'
)
,a2 AS
(
SELECT DISTINCT b.msg
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
SELECT msg=STUFF((SELECT ','+msg FROM a2 FOR XML PATH('')),1,1,'')
unpivot( qty for n in (N1,N2,N3,N4) )as p1
union all
select qty from
(select top 1 * from TEST5 order by N1 desc) as t2
unpivot( qty for n in (N1,N2,N3,N4) )as p2) as t) as t
pivot (sum(qty) for qty in ([11],[22],[33],[44],[55],[66])) as p