在线等,紧急求助

shenchuxue 2015-08-31 12:18:25
原表结构


需要变成的结构


按逗号读取,每个dwbh对应多行jingyfw
也就是每个jingyfw是一行
这个要怎么写,
谢谢!!
...全文
207 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
道素 2016-02-25
我简单弄了几个做例子:

  with t(dwbh,jingyfw) as (
    select 'DWI00000001',N'中成药,化学制剂,生化药品,生物制品' union
	select 'DWI00000002',N'中药片,中药饮片' 
  ),tt(dwbh,xjingyfw) as(
  select t.dwbh,convert(xml,'<r><n>'+replace(t.jingyfw,',','</n><n>')+'</n></r>') from t
  )
  select tt.dwbh,ttt.yp from tt
  cross apply(select  b.value('.','nvarchar(max)') as yp from tt.xjingyfw.nodes('r/n') as s(b)) ttt
结果: dwbh yp DWI00000001 中成药 DWI00000001 化学制剂 DWI00000001 生化药品 DWI00000001 生物制品 DWI00000002 中药片 DWI00000002 中药饮片
回复

declare @tb table(col varchar(50));
insert into @tb
select '10-2-6' union all
select '10-15-6' union all
select '14-8-2' union all
select '1-0-0';
with cte as(
select tb.col,
SUBSTRING(tb.col,s.number+1,(case when CHARINDEX('-',tb.col,s.number+1)>0 then CHARINDEX('-',tb.col,s.number+1)-1 else LEN(tb.col) end)-s.number) as 'a',
_index=len(substring(tb.col,0,s.number+1))-len(replace(substring(tb.col,0,s.number+1),'-',''))
 from @tb tb,master..spt_values s where s.type='p' and s.number between 0 and LEN(tb.col) and ( SUBSTRING(tb.col,s.number,1)='-' or s.number=0)
)
select * from cte
仅供参考
回复
shenchuxue 2016-02-24
谢谢!!!谢谢!!!谢谢!!!谢谢!!!谢谢!!!谢谢!!!谢谢!!!谢谢!!!谢谢!!!
回复
Pact_Alice 2015-08-31
DECLARE @sql VARCHAR(30), @i INT SET @i='DWI00000001' --给变量赋值 SET @sql=(SELECT jingwfw + ',' FROM tabname where dwbh=@i) WHILE Charindex(',', @sql) > 0 BEGIN -- INSERT INTO #t2 SELECT LEFT(@sql, Charindex(',', @sql) - 1) SET @sql= RIGHT(@sql, Len(@sql) - Charindex(',', @sql)) SET @i=@i+1 END
回复
qq_17482963 2015-08-31
引用 4 楼 qq_17482963 的回复:
create table #t1(dwbh nvarchar(50),jingwfw nvarchar(500))
select dwbh,charindex(',',jingwfw,1)pos into #t2 from #t
select dwbh,0 as pos into #t3 from #t

while exists (select * from #t2 where pos<>0)
begin
insert into #t1
select a.dwbh,SUBSTRING(a.jingwfw,c.pos+1,b.pos-c.pos-1) from #t a
left join #t2 b on a.dwbh=b.dwbh
left join #t3 c on a.dwbh=c.dwbh
where b.pos<>0

delete from #t3
insert into #t3 select * from #t2

update a set pos=CHARINDEX(',',b.jingwfw+',',a.pos+1)
from #t2 a inner join #t b on a.dwbh=b.dwbh where a.pos<>0
end
select * from #t1

drop table #t1
drop table #t2
drop table #t3
你試試這個
#t表是你原來的表
回复
qq_17482963 2015-08-31
create table #t1(dwbh nvarchar(50),jingwfw nvarchar(500))
select dwbh,charindex(',',jingwfw,1)pos into #t2 from #t
select dwbh,0 as pos into #t3 from #t

while exists (select * from #t2 where pos<>0)
begin
insert into #t1
select a.dwbh,SUBSTRING(a.jingwfw,c.pos+1,b.pos-c.pos-1) from #t a
left join #t2 b on a.dwbh=b.dwbh
left join #t3 c on a.dwbh=c.dwbh
where b.pos<>0

delete from #t3
insert into #t3 select * from #t2

update a set pos=CHARINDEX(',',b.jingwfw+',',a.pos+1)
from #t2 a inner join #t b on a.dwbh=b.dwbh where a.pos<>0
end
select * from #t1

drop table #t1
drop table #t2
drop table #t3
你試試這個
回复
shenchuxue 2015-08-31
还有大咖能指点一下不? 谢谢…………
回复
qq_17482963 2015-08-31
參考這個 http://bbs.csdn.net/topics/391817728
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-31 12:18
社区公告
暂无公告