--测试表与数据 declare @t table(id int,strname varchar(1000)) insert into @t select 1 ,'abc,123,4frf' union select 2 ,'eee,ddd'--生成辅助临时表,注意数据行的多少,根据实际修改。 select top 10000 [id]=identity(int,1,1) into #temp from (select top 100 id from syscolumns) a, (select top 100 id from syscolumns) b --查询出结果,如果需要筛选var_为空的或为0的则需要在此结果集上筛选.需要整型的,var_后加convert(int,...) select a.id,strname=substring(a.strname,b.[id],charindex(',',a.strname+',',b.[id])-b.[id]) from @t a,#temp b where substring(','+a.strname,b.[id],1)=',' order by a.id --删除临时表 drop table #temp/*结果 1 abc 1 123 1 4frf 2 ddd 2 eee */
create table a (id int, strname char(20)) insert a select 1 ,'abc,123,4frf' union all select 2 ,'eee,ddd'create function cx(@id int) returns @r table(id int,strname char(20)) as begin declare @strname char(50) select @strname=strname from a where @id=id while charindex(',',@strname)>0 begin insert @r select @id,left(@strname,charindex(',',@strname)-1) set @strname=stuff(@strname,1,charindex(',',@strname),'') end insert @r select @id,@strname return end godeclare @id int set @id=1 select * from cx(@id)select * into # from a where 1=2 declare @i int,@id int select @id=min(id) from a select @i=count(1) from a while @i>0 begin insert # select * from cx(@id) select @id=min(id) from a where id>@id set @i=@i-1 endselect * from #drop table # drop function cx drop table aid strname ----------- -------------------- 1 abc 1 123 1 4frf 2 eee 2 ddd (所影响的行数为 5 行)
create function List(@id int,@strname varchar(20)) returns varchar(800) begin declare @i int,@id_no int,@fH_pst int,@out varchar(800) @i=0 @id_no = 1 @fH_pst=0 @out=cast(@id as char(4)) +space(5)+ @strname + chr(13) while(@i<len(@strname) begn if(substring(@strname,i,1)<>',') begin @i=@i+1 continue end else begin if(@fH_pst=0) begin @out=@out+cast(@id_no as char(4))+space(5)+left(@strname) @fH_pst=@i end else begin @out=@out+cast(@id_no as char(4))+space(5)+substring(@strname,@fH_Pst,@i) @fH_pst=@i end end end return @out end select dbo.List(id,strname) from tb order by id
写了一自定义函数,但是不知道怎么在SQL语句中调用。 CREATE function f_split(@id char(6),@c varchar(2000)) returns @t table(id char(6),col varchar(20)) as begin while(charindex(@split,@c)<>0) begin insert @t(id,col) values (@id,substring(@c,1,charindex(',',@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(id,col) values (@id,@c) return end
declare @t table(id int,strname varchar(1000))
insert into @t
select 1 ,'abc,123,4frf' union
select 2 ,'eee,ddd'--生成辅助临时表,注意数据行的多少,根据实际修改。
select top 10000 [id]=identity(int,1,1) into #temp
from (select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) b
--查询出结果,如果需要筛选var_为空的或为0的则需要在此结果集上筛选.需要整型的,var_后加convert(int,...)
select a.id,strname=substring(a.strname,b.[id],charindex(',',a.strname+',',b.[id])-b.[id])
from @t a,#temp b
where substring(','+a.strname,b.[id],1)=','
order by a.id
--删除临时表
drop table #temp/*结果
1 abc
1 123
1 4frf
2 ddd
2 eee
*/
(id int, strname char(20))
insert a
select 1 ,'abc,123,4frf'
union all
select 2 ,'eee,ddd'create function cx(@id int)
returns @r table(id int,strname char(20))
as
begin
declare @strname char(50)
select @strname=strname from a where @id=id
while charindex(',',@strname)>0
begin
insert @r
select @id,left(@strname,charindex(',',@strname)-1)
set @strname=stuff(@strname,1,charindex(',',@strname),'')
end
insert @r
select @id,@strname
return
end
godeclare @id int
set @id=1
select * from cx(@id)select * into # from a where 1=2
declare @i int,@id int
select @id=min(id) from a
select @i=count(1) from a
while @i>0
begin
insert # select * from cx(@id)
select @id=min(id) from a where id>@id
set @i=@i-1
endselect * from #drop table #
drop function cx
drop table aid strname
----------- --------------------
1 abc
1 123
1 4frf
2 eee
2 ddd (所影响的行数为 5 行)
returns varchar(800)
begin
declare @i int,@id_no int,@fH_pst int,@out varchar(800)
@i=0
@id_no = 1
@fH_pst=0
@out=cast(@id as char(4)) +space(5)+ @strname + chr(13)
while(@i<len(@strname)
begn
if(substring(@strname,i,1)<>',')
begin
@i=@i+1
continue
end
else
begin
if(@fH_pst=0)
begin
@out=@out+cast(@id_no as char(4))+space(5)+left(@strname)
@fH_pst=@i
end
else
begin
@out=@out+cast(@id_no as char(4))+space(5)+substring(@strname,@fH_Pst,@i)
@fH_pst=@i
end
end
end
return @out
end
select dbo.List(id,strname) from tb order by id
CREATE function f_split(@id char(6),@c varchar(2000))
returns @t table(id char(6),col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(id,col) values (@id,substring(@c,1,charindex(',',@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(id,col) values (@id,@c)
return
end