在线等，紧急求助

[shenchuxue]

原表结构


需要变成的结构


按逗号读取，每个dwbh对应多行jingyfw
也就是每个jingyfw是一行
这个要怎么写，
谢谢！！

[qq_17482963]

參考這個 http://bbs.csdn.net/topics/391817728

[shenchuxue]

还有大咖能指点一下不？ 谢谢…………

[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

你試試這個

[qq_17482963]

引用 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表是你原來的表

[Pact_Alice]

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

[shenchuxue]

谢谢！！！谢谢！！！谢谢！！！谢谢！！！谢谢！！！谢谢！！！谢谢！！！谢谢！！！谢谢！！！

[骑着蜗牛去爬山]

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

仅供参考

[道素]

我简单弄了几个做例子：

  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 中药饮片