SQL2008一行转多行的精典写法

[size=x-large]--创建测试表
create table t1( id int, txt varchar(30))
--插入测试数据

insert into t1 select 1,'aa,dd,f' UNION
SELECT
2,'ddd,ff,rt,gg'
union SELECT
3,'s,dddddee,d'
union SELECT
4,'ty'
union SELECT
5,'hello,dderf,kii'
union SELECT
6,'da,ddeff,ddd,d'


select * from t1
--一行变多行的方法
select
a.id,b.txt
from
(select id,txt=convert(xml,'<root><v>'+replace(txt,',','</v><v>')+'</v></root>') from t1
)a
outer apply
(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b[/size]