原文链接：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 行受影响)
*/
