有表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