SQL2005实现列折解成多行

 /*参考爱新觉罗.毓华

ID         col
1       aa,bb,cc,dd,ee
2       aaa,bbb,ccc
3       aaaa,bbbb

得到如下结果：
Id     Col
--- -------
1      aa
1      bb
1      cc
1      dd
1      ee
2      aaa
2      bbb
2      ccc
3      aaaa
3      bbbb
---------------------
*/

create table #mytb(Id int,col varchar(100))
insert into #mytb values(1,'aa,bb,cc,dd,ee')
insert into #mytb values(2,'aaa,bbb,ccc')
insert into #mytb values(3,'aaaa,bbbb')

--
select Id,B.col
from(
 select Id,col=convert(xml,'<root><v>' + replace(col, ',', '</v><v>') + '</v></root>') from #mytb
)A
outer apply(
 select col = N.v.value('.', 'varchar(100)') from A.col.nodes('/root/v') N(v)
)B

drop table #mytb