有表tb, 如下:
id value
———– ———–
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
———– ——–
1 aa
1 bb
2 aaa
2 bbb
2 ccc
–方法1.使用xml完成
SELECT A.id, B.value FROM
(
SELECT id, [value] = CONVERT(xml,'<root><v>’ + REPLACE([value], ‘,’, ‘</v><v>’) + ‘</v></root>’) FROM tb
) A OUTER APPLY
(
SELECT value = N.v.value(‘.’, ‘varchar(100)’) FROM A.[value].nodes(‘/root/v’) N(v)
) B
–方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(‘,’,[value]+’,’)-1) as nvarchar(100)),Split=cast(stuff([value]+’,’,1,charindex(‘,’,[value]+’,’),”) as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(‘,’,Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(‘,’,Split),”) as nvarchar(100)) from tt where split>”
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb