create table A(course varchar(20),name varchar(20))
insert A select 'a','10;20;30'
insert A select 'b','10;50'
insert A select 'c','20;' create proc ptest
as begin
set nocount on
declare @i as int
declare @a varchar(20)
declare @t table(course varchar(20),name varchar(20))
declare @course varchar(20)
declare @s varchar(20)
set @course=''
while @course is not null
begin
select @course=min(course) from A where course>@course
if @course is not null begin
select @s=name from A where course=@course
set @i=charindex(';',@s)
while @i>0
begin
set @a=left(@s,@i-1)
insert @t values(@course,@a)
set @s=right(@s,len(@s)-@i)
set @i=charindex(';',@s)
end
if len(@s)>0
begin
insert @t values(@course,@s)
end
end
end
select * from @t
set nocount off
end exec ptest
insert A select 'a','10;20;30'
insert A select 'b','10;50'
insert A select 'c','20;' create proc ptest
as begin
set nocount on
declare @i as int
declare @a varchar(20)
declare @t table(course varchar(20),name varchar(20))
declare @course varchar(20)
declare @s varchar(20)
set @course=''
while @course is not null
begin
select @course=min(course) from A where course>@course
if @course is not null begin
select @s=name from A where course=@course
set @i=charindex(';',@s)
while @i>0
begin
set @a=left(@s,@i-1)
insert @t values(@course,@a)
set @s=right(@s,len(@s)-@i)
set @i=charindex(';',@s)
end
if len(@s)>0
begin
insert @t values(@course,@s)
end
end
end
select * from @t
set nocount off
end exec ptest
declare @re table(col1 varchar(10),col2 varchar(30))
insert into @t select 'a','10;20;30;'
union all select 'b','10;50;'
union all select 'c','20;'
declare @col1 varchar(8000)
declare @col2 varchar(8000)
declare @ls_col2 varchar(1000)
declare @li_pos int
declare c_cur cursor for select a,b from @t
open c_cur
fetch next from c_cur into @col1,@col2
while @@FETCH_STATUS=0
begin
set @ls_col2=@col2
set @li_pos=charindex(';',@col2,1)
while @li_pos>0
begin
set @ls_col2=substring(@col2,1,@li_pos - 1)
insert into @re select @col1,@ls_col2
set @col2=STUFF(@col2,1,@li_pos,'')
set @li_pos=charindex(';',@col2,1)
end
fetch next from c_cur into @col1,@col2
endselect * from @re
/*col1 col2
---------- ------------------------------
a 10
a 20
a 30
b 10
b 50
c 20 */
set @re='10;20;30'
declare @sql varchar(100)
set @sql=''
select @sql=@sql+'select col='+''''+replace(@re,';','''union all select''')+''''
exec(@sql)
但满足不了需求还是支持 wangkenping(找有感觉的妹妹)
我也是这个思路
select L1=L,N=col2 from table where N2 is not null union
select L1=L,N=col3 from table where N3 is not null
(id varchar(100),pp varchar(100))
insert a
select'a' ,'10;20;30;'
union all
select'b' ,'10;50;'
union all
select'c' ,'20;'create function cx (@id varchar(100))
returns @r table (id varchar(100),pp varchar(100))
as
begin
declare @pp varchar(100)
select @pp=pp from a where @id=id
while charindex(';',@pp)>0
begin
insert @r
select @id,left(@pp,charindex(';',@pp)-1)
set @pp=stuff(@pp,1,charindex(';',@pp),'')
end
insert @r
select @id,@pp
return
end
go
select * into # from a where 1=2
declare @i int,@id varchar(100)
select @i=count(1) from a
select @id=min(id) from a
while @i>0
begin
insert into #
select * from dbo.cx(@id)
select @id=min(id) from a where @id<id
set @i=@i-1
end
goselect * from #
where pp<>''
create table A(course varchar(20),name varchar(20))
insert A select 'a','10;20;30'
insert A select 'b','10;50'
insert A select 'c','20;' --替换SQL(少量数据可以采用)
declare @result varchar(8000)
set @result=''
select @result=@result+'union all select '''+course+''','''+replace(name,';',''' union all select '''+course+''', ''')+''' ' from A
set @result=stuff(@result,1,9,'')
--临时表.
create table #(column1 varchar(10),column2 varchar(10))
insert into # exec(@result)
--查看结果
select * from # where column2<>''--结果
column1 column2
---------- ----------
a 10
a 20
a 30
b 10
b 50
c 20(所影响的行数为 6 行)
Begin
Insert Into #Result
Select column1,SubString(column2,1,CharIndex(';',column2+';')-1)
From table1
Where Isnull(column2,'')<>''
Update table1
Set column2=SubString(column2,CharIndex(';',column2+';')+1,Len(column2))
Where Isnull(column2,'')<>''
Endtruncate table table1
Insert Into table1
Select * From #ResultDrop Table #Result