sql 结果折分，一行变多行

资料来源：http://bbs.csdn.net/topics/300070928

if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'a,f'
Go

select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b

运行结果：

(3 行受影响)
COl1        Col2
----------- ----------------------------------------------------------------------------------------------------
1           a
1           b
1           c
2           d
2           e
3           a
3           f

(7 行受影响)