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_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

不知道怎么提交代码！！！！

以上是用游标实现的

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