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]