表中某字段按分隔符拆分为多行
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
SELECT * FROM @TB
C1 C2 C3
----------- -------------------- --------------------
1 A a,b,c
2 A a,b
3 B a,b
4 C a,b
想实现结果为：
C1 C2 C3
----------- -------------------- --------------------
1 A a
1 A b
1 A c
2 A a
2 A b
3 B a
3 B b
4 C a
4 C b
DECLARE @TB AS TABLE (C1 INT,C2 VARCHAR(20),C3 VARCHAR(200))
INSERT INTO @TB(C1,C2,C3)
SELECT 1 AS C1,'A','a,b,c'
UNION
SELECT 2,'A','a,b'
UNION
SELECT 3,'B','a,b'
UNION
SELECT 4,'C','a,b'
SELECT c1,c2,b.c3
FROM
(SELECT c1,c2,c3=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(c3)),',','</v><v>')+'</v></root>') FROM @TB) a
OUTER APPLY
(SELECT c3 = C.v.value('.','NVARCHAR(MAX)') FROM a.c3.nodes('/root/v') C(v)) b
