首页 新闻 搜索 专区 学院

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
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
yj_smile的主页 yj_smile | 菜鸟二级 | 园豆:228
提问于:2012-09-12 15:47
< >
分享
最佳答案
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通过节点把他分离显示 结果很成功

 


供大家参考额 希望大家提出更好的解决办法 交流下

yj_smile | 菜鸟二级 |园豆:228 | 2012-09-13 11:31

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 | 园豆:202 (菜鸟二级) | 2015-01-21 14:38
其他回答(3)
0

pivot

收获园豆:2
哇~怪兽 | 园豆:603 (小虾三级) | 2012-09-12 16:27
0

使用函数分隔,然后查询。
参考:http://www.cnblogs.com/dataadapter/archive/2012/07/19/2598506.html

收获园豆:3
acepro | 园豆:1218 (小虾三级) | 2012-09-12 18:23

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

支持(0) 反对(0) yj_smile | 园豆:228 (菜鸟二级) | 2012-09-13 11:32
 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

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

以上是用游标实现的

支持(0) 反对(0) MaxGeek | 园豆:202 (菜鸟二级) | 2015-01-21 14:45

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

支持(0) 反对(0) yj_smile | 园豆:228 (菜鸟二级) | 2015-01-21 14:48
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

MaxGeek | 园豆:202 (菜鸟二级) | 2015-01-21 14:36
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册