sqlServer 2008 一行转多行的问题怎么解决呢？

Question

现有如下一张表

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

 

我现在解决是解决了  但是很笨的方法 求解 稍后附上自己代码 谢谢！

Answer 1

pivot

Answer 2

你的结果跟我的问题 不太相符 不过还是谢谢了

Answer 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_1open mycurdeclare @t_txt varchar(50),@t_txt1 varchar(50)fetch next from mycur into @t_txtwhile(@@FETCH_STATUS = 0)begin if CHARINDEX(',',@t_txt)> 0begin  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)endelsebegininsert #table_temp(txt) values(@t_txt)endfetch next from mycur into @t_txtendclose mycurdeallocate mycurselect * from #table_tempdrop table  #table_temp不知道怎么提交代码！！！！ 以上是用游标实现的

Answer 4

@MaxGeek: 游标效率太低的 在文章结尾，我已经给出了最佳解决方案 ，请知悉

Answer 5

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