把一条sql语句记录拆分为多条记录

if exists (select 1 from dbo.sysobjects where id = object_id(N'test'))
drop procedure [dbo].[test]
GO
Create table test ( id int primary key,LongField varchar(50))
GO
Insert Into test (id,LongField) values('1','aa bb cc dd')
Insert Into test (id,LongField) values('3','ab cc da fm')
GO
if exists (select 1 from dbo.sysobjects where id = object_id(N'f_tstr'))
drop procedure [dbo].[f_tstr]
GO
create function f_tstr(
@s varchar(8000),
@split varchar(10),
@id int )returns @re table(id int ,col varchar(100))
as
begin
if @s is null return
while charindex(@split,@s)>0
begin
insert into @re(id,col)select @id,left(@s,charindex(@split,@s)-1)
set @s=right(@s,len(@s)-charindex(@split,@s))
end
insert into @re(id,col) select @id, @s return
end
GO
if exists (select 1 from dbo.sysobjects where id = object_id(N'test1'))
drop procedure [dbo].[test1]
GO
Create table test1 ( id int,ShortField varchar(50))
GO
declare @M int
select @M=max(id) from test
while @M>0
begin
declare @a varchar (200)
,@b varchar(20)
select @a=id,@b=LongField from test where id =@M
insert into test1(id,ShortField) select * from dbo.f_tstr(@b,' ',@a)
set @M=@M-1
set @a=null
set @b=null
end
GO
select * from test1
GO
drop table test
drop table test1
drop function DBO.f_tstr
GO
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/u013715860/article/details/147065273