SQL语句 拆分某些字段,一行变多行

原文链接：http://bbs.csdn.net/topics/310219852

 

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-08-13 09:49:48
-------------------------------------

--> 生成测试数据: @T
DECLARE @T TABLE (a1 INT,a2 VARCHAR(5),a3 VARCHAR(8))
INSERT INTO @T
SELECT 1,'a','一' UNION ALL
SELECT 2,'a,b','一,二' UNION ALL
SELECT 3,'a,b,c','一,二,三'

--SQL查询如下:

;WITH Liang AS
(
SELECT T.a1,T.a2,T.a3,
A.x.value('.','varchar(20)') AS a4,T.a5,
        ROW_NUMBER() OVER(PARTITION BY T.a1 ORDER BY T.a1) AS rowid
FROM (
SELECT a1,a2,a3,a4=CONVERT(XML,'<v>'+REPLACE(a2,',','</v><v>')+'</v>'),
 a5=CONVERT(XML,'<v>'+REPLACE(a3,',','</v><v>')+'</v>')
FROM @T
) AS T
OUTER APPLY a4.nodes('//v') AS A(x)
)
SELECT a1,a2,a3,a4,
    a5=a5.value('(//v[position()=sql:column("rowid")])[1]','varchar(20)')
FROM Liang

/*
a1          a2    a3       a4                   a5
----------- ----- -------- -------------------- --------------------
1           a     一        a                    一
2           a,b   一,二      a                    一
2           a,b   一,二      b                    二
3           a,b,c 一,二,三    a                    一
3           a,b,c 一,二,三    b                    二
3           a,b,c 一,二,三    c                    三

(6 行受影响)
*/