求一个SQL的写法,,高手们帮帮忙

[nthb2002]

一个表的问题
有表1:

列1 列2 列3
A1|A2|A3|A4 2000 2098
A1|A2 1900 1821
A3 1500 1432

要得到如下格式:

列1 列2 列3
A1 2000 2098
A2 2000 2098
A3 2000 2098
A4 2000 2098
A1 1900 1821
A2 1900 1821
A3 1500 1432

[rfq]

利用游标循环分解插入表中

[nthb2002]

....谢谢,,,

在找不用游标的方法,,,

游标太慢,,

[wangkenping]

declare @t table(c1 varchar(200),c2 int,c3 int)
insert into @t select 'A1|A2|A3|A4',2000,2098
union all select 'A1|A2',1900,1821
union all select 'A3',1500,1432

declare @ls_c1 varchar(200)
declare @li_c2 int
declare @li_c3 int
declare @ls_tmp varchar(20)
declare @li_find int
declare c_cur cursor for
select c1,c2,c3 from @t a where charindex('|',a.c1)>0
open c_cur
fetch next from c_cur into @ls_c1,@li_c2,@li_c3
set @ls_c1=@ls_c1+'|'
while @@fetch_status=0
begin

set @li_find=charindex('|',@ls_c1)
while @li_find>0
begin
set @ls_tmp=substring(@ls_c1,1,@li_find -1)
insert into @t (c1,c2,c3) values (@ls_tmp,@li_c2,@li_c3)
set @ls_c1=substring(@ls_c1,@li_find +1,100)
set @li_find=charindex('|',@ls_c1)
end
set @li_find=0
fetch next from c_cur into @ls_c1,@li_c2,@li_c3
set @ls_c1=@ls_c1+'|'
end

close c_cur
DEALLOCATE c_cur

select * from @t a where charindex('|',a.c1)=0


c1 c2 c3
-------- -----------
A3 1500 1432
A1 2000 2098
A2 2000 2098
A3 2000 2098
A4 2000 2098
A1 1900 1821
A2 1900 1821

（所影响的行数为 7 行）

[nthb2002]

谢一个,,,

不用游标有没有其它的方法,,