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

 

if exists (select 1 from dbo.sysobjects where id = object_id(N't_test'))
drop procedure [dbo].[t_test]
GO
Create table t_test ( id int primary key,LongField varchar(50))
GO
Insert Into t_test (id,LongField)  values('1','aa bb cc dd')
Insert Into t_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't_test1'))
drop procedure [dbo].[t_test1]
GO
Create table t_test1 ( id int,ShortField varchar(50))
GO
declare @M int
select @M=max(id) from t_test
while @M>0
        begin                
        declare @a varchar (200)
                ,@b varchar(20)
        select @a=id,@b=LongField from t_test where id =@M        
        insert into t_test1(id,ShortField) select * from dbo.f_tstr(@b,' ',@a)
        set @M=@M-1
        set @a=null
        set @b=null
        end
GO
select * from t_test1
GO
drop table t_test
drop table t_test1
drop function DBO.f_tstr
GO

文章出处：

http://blog.csdn.net/zyyjc/article/details/7577088