sqlServer 2008 一行转多行的问题怎么解决呢？
0
悬赏园豆：5
[已解决问题]
解决于 2012-09-21 11:07
现有如下一张表
id txt
1 ed
2 aa,dd
3 xx,yy,asdfg
4 dfgf,dfkgjj
5 ax,fg,aaaaaaa,dfkgjj
要求变成
id txt
1 ed
2 aa
2 dd
3 xx
3 yy
3 asdfg
4 dfgf
4 dfkgjj
5 ax
5 fg
5 aaaaaaa
5 dfkgjj
我现在解决是解决了 但是很笨的方法 求解 稍后附上自己代码 谢谢！
问题补充：
Sql Code
Sql Code
select a.id,b.txt from (select id,txt=convert(xml,'<root><v>'+replace(txt,',','</v><v>')+'</v></root>') from showTablename )a outer apply (select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b
最佳答案
0
select
a.id,b.txt
from
(select id,txt=convert(xml,'<root><v>'+replace(txt,',','</v><v>')+'</v></root>') from showTablename
)a
outer apply
(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b
你们看 这是用sql xml最终写出来的，是不是很简单
我用节点符号替换了逗号，然后用 C.V.value通过节点把他分离显示 结果很成功
供大家参考额 希望大家提出更好的解决办法 交流下
create table #table_temp(
id int identity(1,1) not null,
txt varchar(50));
declare mycur cursor for select txt from Table_1
open mycur
declare @t_txt varchar(50),@t_txt1 varchar(50)
fetch next from mycur into @t_txt
while(@@FETCH_STATUS = 0)
begin
if CHARINDEX(',',@t_txt)> 0
begin
while(CHARINDEX(',',@t_txt)> 0 )
begin
set @t_txt1 = substring(@t_txt,1,CHARINDEX(',',@t_txt)-1)
insert #table_temp(txt) values(@t_txt1)
set @t_txt = SUBSTRING(@t_txt,CHARINDEX(',',@t_txt)+1,LEN(@t_txt) - LEN(@t_txt1))
end
insert into #table_temp(txt) values (@t_txt)
end
else
begin
insert #table_temp(txt) values(@t_txt)
end
fetch next from mycur into @t_txt
end
close mycur
deallocate mycur
select * from #table_temp
drop table #table_temp
不知道怎么提交代码！！！！
以上是用游标实现的
其他回答(3)
0
pivot
收获园豆：2
0
收获园豆：3
你的结果跟我的问题 不太相符 不过还是谢谢了
1 select * from Table_1; 2 create table #table_temp( 3 id int identity(1,1) not null, 4 txt varchar(50)); 5 declare mycur cursor for select txt from Table_1 6 open mycur 7 declare @t_txt varchar(50),@t_txt1 varchar(50) 8 fetch next from mycur into @t_txt 9 while(@@FETCH_STATUS = 0) 10 begin 11 if CHARINDEX(',',@t_txt)> 0 12 begin 13 while(CHARINDEX(',',@t_txt)> 0 ) 14 begin 15 set @t_txt1 = substring(@t_txt,1,CHARINDEX(',',@t_txt)-1) 16 insert #table_temp(txt) values(@t_txt1) 17 set @t_txt = SUBSTRING(@t_txt,CHARINDEX(',',@t_txt)+1,LEN(@t_txt) - LEN(@t_txt1)) 18 end 19 insert into #table_temp(txt) values (@t_txt) 20 end 21 else 22 begin 23 insert #table_temp(txt) values(@t_txt) 24 end 25 fetch next from mycur into @t_txt 26 end 27 close mycur 28 deallocate mycur 29 select * from #table_temp 30 drop table #table_temp
@yj_smile: create table #table_temp(
id int identity(1,1) not null,
txt varchar(50));
declare mycur cursor for select txt from Table_1
open mycur
declare @t_txt varchar(50),@t_txt1 varchar(50)
fetch next from mycur into @t_txt
while(@@FETCH_STATUS = 0)
begin
if CHARINDEX(',',@t_txt)> 0
begin
while(CHARINDEX(',',@t_txt)> 0 )
begin
set @t_txt1 = substring(@t_txt,1,CHARINDEX(',',@t_txt)-1)
insert #table_temp(txt) values(@t_txt1)
set @t_txt = SUBSTRING(@t_txt,CHARINDEX(',',@t_txt)+1,LEN(@t_txt) - LEN(@t_txt1))
end
insert into #table_temp(txt) values (@t_txt)
end
else
begin
insert #table_temp(txt) values(@t_txt)
end
fetch next from mycur into @t_txt
end
close mycur
deallocate mycur
select * from #table_temp
drop table #table_temp
不知道怎么提交代码！！！！
以上是用游标实现的
@MaxGeek: 游标效率太低的 在文章结尾，我已经给出了最佳解决方案 ，请知悉
0
create table #table_temp( id int identity(1,1) not null, txt varchar(50)); declare mycur cursor for select txt from Table_1 open mycur declare @t_txt varchar(50),@t_txt1 varchar(50) fetch next from mycur into @t_txt while(@@FETCH_STATUS = 0) begin if CHARINDEX(',',@t_txt)> 0 begin while(CHARINDEX(',',@t_txt)> 0 ) begin set @t_txt1 = substring(@t_txt,1,CHARINDEX(',',@t_txt)-1) insert #table_temp(txt) values(@t_txt1) set @t_txt = SUBSTRING(@t_txt,CHARINDEX(',',@t_txt)+1,LEN(@t_txt) - LEN(@t_txt1)) end insert into #table_temp(txt) values (@t_txt) end else begin insert #table_temp(txt) values(@t_txt) end fetch next from mycur into @t_txt end close mycur deallocate mycur select * from #table_temp drop table #table_temp
