所以我有一个包含以下列的表
| 成员 | 版本 | |
|---|---|---|
| 1 | 25 | |
| 2 | 18 | |
| 3 | 316 | |
| 1 | 20 | |
| 3 | 40 | |
| 1 | 316 | |
| 3 | 65 | |
| 2 | 27 | |
| 3 | 65 |
首先创建cte
with a as(
Select a, b from #Temp
where b between 15 and 20
or b in (40, 316)
or b between 65 and 66)
则使用CCD_ 2如下
SELECT DISTINCT a2.a,
SUBSTRING(
(
SELECT ','+cast(a1.b as varchar(100)) AS [text()]
FROM a a1
WHERE a1.a = a2.a
ORDER BY a1.a
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [b]
FROM a a2