34,590
社区成员
发帖
与我相关
我的任务
分享
--2.2005以上新方法:
SELECT a.id,b.col
FROM (
SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS XML)
FROM tb
) AS a
OUTER APPLY (
SELECT C.value('.','varchar(50)') AS col --此处value必须为小写
FROM a.col.nodes('/v') AS T(C)
) AS b
--结果:
/*
id col
----------- --------------------------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/
http://blog.csdn.net/jinjazz/archive/2009/09/01/4507720.aspx