declare @a table (id int identity ,a int ,b int ,c int ) declare @v int set @v=100 while @v >0begin insert into @a values(1,2,3) insert into @a values(4,5,6) insert into @a values(7,8,9) set @v=@v-1 end select id/5+1 as id,a,b,c i into t from @a ---------借用 create function f_union(@id int) returns varchar(100) as begin declare @sql varchar(100) set @sql='' select @sql=@sql+' '+cast(a as varchar(10))+cast(a as varchar(10))+cast(a as varchar(10)) from t where id=@id order by id return @sql end goselect id,dbo.f_union(id) as test from t group by id drop function f_union drop table t
不好意思,再发一次 此函数为借用 create function f_union(@id int) returns varchar(100) as begin declare @sql varchar(100) set @sql='' select @sql=@sql+' '+cast(a as varchar(1))+cast(b as varchar(1))+cast(c as varchar(1)) from t where id=@id order by id return @sql end godeclare @a table (id int identity ,a int ,b int ,c int ) declare @v int set @v=100 while @v >0begin insert into @a values(1,2,3) insert into @a values(4,5,6) insert into @a values(7,8,9) set @v=@v-1 end select * from @a select (id-1)/5+1 as id,a,b,c into t from @a select * from tselect id,dbo.f_union(id) as test from t group by id drop function f_union drop table t
create table #1(id int,value int,p int)insert into #1 select 1,2,3 union all select 4,5,6 union all select 7,8,9 union all select 10,11,12 union all select 13,14,15 union all select 17,18,19select pid=identity(int,1,1),* into #2 from #1select a.id,a.value,a.p,b.id,b.value,b.p,c.id,c.value,c.p,d.id,d.value,d.p,e.id,e.value,e.p from #2 a left join #2 b on b.pid=a.pid+1 left join #2 c on c.pid=b.pid+1 left join #2 d on d.pid=c.pid+1 left join #2 e on e.pid=d.pid+1 where a.pid%5=1--删除临时表 drop table #1 drop table #2
改进下: create table #1(id int,value int,p int)insert into #1 select 1,2,3 union all select 4,5,6 union all select 7,8,9 union all select 10,11,12 union all select 13,14,15 union all select 17,18,19select pid=identity(int,1,1),* into #2 from #1select a.id,a.value,a.p,b.id,b.value,b.p,c.id,c.value,c.p,d.id,d.value,d.p,e.id,e.value,e.p from (select * from #2 a where a.pid%5=1)a left join #2 b on b.pid=a.pid+1 left join #2 c on c.pid=b.pid+1 left join #2 d on d.pid=c.pid+1 left join #2 e on e.pid=d.pid+1--删除临时表 drop table #1 drop table #2
declare @v int
set @v=100
while @v >0begin
insert into @a values(1,2,3)
insert into @a values(4,5,6)
insert into @a values(7,8,9)
set @v=@v-1
end
select id/5+1 as id,a,b,c i into t from @a
---------借用
create function f_union(@id int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+' '+cast(a as varchar(10))+cast(a as varchar(10))+cast(a as varchar(10)) from t where id=@id order by id
return @sql
end
goselect id,dbo.f_union(id) as test from t group by id
drop function f_union
drop table t
不好意思,再发一次
此函数为借用
create function f_union(@id int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+' '+cast(a as varchar(1))+cast(b as varchar(1))+cast(c as varchar(1)) from t where id=@id order by id
return @sql
end
godeclare @a table (id int identity ,a int ,b int ,c int )
declare @v int
set @v=100
while @v >0begin
insert into @a values(1,2,3)
insert into @a values(4,5,6)
insert into @a values(7,8,9)
set @v=@v-1
end select * from @a
select (id-1)/5+1 as id,a,b,c into t from @a
select * from tselect id,dbo.f_union(id) as test from t group by id
drop function f_union
drop table t
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9
union all
select 10,11,12
union all
select 13,14,15
union all
select 17,18,19select pid=identity(int,1,1),* into #2 from #1select a.id,a.value,a.p,b.id,b.value,b.p,c.id,c.value,c.p,d.id,d.value,d.p,e.id,e.value,e.p
from #2 a
left join #2 b
on b.pid=a.pid+1
left join #2 c
on c.pid=b.pid+1
left join #2 d
on d.pid=c.pid+1
left join #2 e
on e.pid=d.pid+1
where a.pid%5=1--删除临时表
drop table #1
drop table #2
create table #1(id int,value int,p int)insert into #1
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9
union all
select 10,11,12
union all
select 13,14,15
union all
select 17,18,19select pid=identity(int,1,1),* into #2 from #1select a.id,a.value,a.p,b.id,b.value,b.p,c.id,c.value,c.p,d.id,d.value,d.p,e.id,e.value,e.p
from
(select * from #2 a where a.pid%5=1)a
left join #2 b
on b.pid=a.pid+1
left join #2 c
on c.pid=b.pid+1
left join #2 d
on d.pid=c.pid+1
left join #2 e
on e.pid=d.pid+1--删除临时表
drop table #1
drop table #2