--create testf1 table create table testf1(f1 char(1)) ---Insert data insert into testf1 values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'); --Create destination table create table testf2(f1 char(1),f2 char(1),f3 char(1)) --temp table create table #testf2(f1 char(1),f2 char(1),f3 char(1),ID int)select ROW_NUMBER() over(order by f1)%3 as [余数],ROW_NUMBER() over(order by f1) as [Num],f1 into #test1 from testf1 insert into #testf2(f1,ID) select f1,Num from #test1 where [余数] = 1 --更新f2 column update #testf2 set f2 = t1.f1 from #testf2 f2 inner join #test1 t1 on t1. Num = f2.ID +1 --更新f3 column update #testf2 set f3 = t1.f1 from #testf2 f2 inner join #test1 t1 on t1. Num = f2.ID +2 ---Insert into destination table insert into testf2 select ISNULL(f1,'') as f1, ISNULL(f2,'') as f2,ISNULL(f3,'') as f3 from #testf2select * from testf2 f1 f2 f3 ---- ---- ---- a b c d e f g h i j (4 row(s) affected)
create table testf1(f1 char(1)) insert into testf1 select 'a' union all select 'b'union all select 'c'union all select 'd'union all select 'e'union all select 'f'union all select 'g'union all select 'h'union all select 'i'union all select 'j';--sql 2005 ;with cte as ( select f1,rid=row_number() over (order by getdate()) from testf1 )select a.f1 as af,b.f1 as bf,c.f1 as cf from (select *,(rid-1)/3 as fg from cte where (rid-1)%3=0) a left join (select *,(rid-1)/3 as fg from cte where (rid-1)%3=1) b on a.fg = b.fg left join (select *,(rid-1)/3 as fg from cte where (rid-1)%3=2) c on a.fg = c.fg--sql 2000select f1,rid=identity(int,1,1) into #testf1 from testf1select a.f1 as af,b.f1 as bf,c.f1 as cf from (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=0) a left join (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=1) b on a.fg = b.fg left join (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=2) c on a.fg = c.fgdrop table #testf1 drop table testf1/*****************************(10 行受影响) af bf cf ---- ---- ---- a b c d e f g h i j NULL NULL(4 行受影响)(10 行受影响)af bf cf ---- ---- ---- a b c d e f g h i j NULL NULL(4 行受影响)
DECLARE @A TABLE([F1] VARCHAR(1)) INSERT @A SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'F' UNION ALL SELECT 'G' UNION ALL SELECT 'H' UNION ALL SELECT 'I' UNION ALL SELECT 'J';WITH M1 AS ( SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS ID,* FROM @A ),M2 AS ( SELECT ID%3 AS RID, ROW_NUMBER () OVER (PARTITION BY ID%3 ORDER BY ID) AS NID, * FROM M1 ) SELECT MAX(CASE WHEN RID=1 THEN F1 ELSE '' END) AS F1, MAX(CASE WHEN RID=2 THEN F1 ELSE '' END) AS F2, MAX(CASE WHEN RID=0 THEN F1 ELSE '' END) AS F3 FROM M2 GROUP BY NID /* F1 F2 F3 ---- ---- ---- A B C D E F G H I J */
--create testf1 table
create table testf1(f1 char(1))
---Insert data
insert into testf1 values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j');
--Create destination table
create table testf2(f1 char(1),f2 char(1),f3 char(1))
--temp table
create table #testf2(f1 char(1),f2 char(1),f3 char(1),ID int)select ROW_NUMBER() over(order by f1)%3 as [余数],ROW_NUMBER() over(order by f1) as [Num],f1 into #test1 from testf1 insert into #testf2(f1,ID) select f1,Num from #test1 where [余数] = 1 --更新f2 column
update #testf2 set f2 = t1.f1 from #testf2 f2 inner join #test1 t1 on t1. Num = f2.ID +1
--更新f3 column
update #testf2 set f3 = t1.f1 from #testf2 f2 inner join #test1 t1 on t1. Num = f2.ID +2
---Insert into destination table
insert into testf2
select ISNULL(f1,'') as f1, ISNULL(f2,'') as f2,ISNULL(f3,'') as f3 from #testf2select * from testf2 f1 f2 f3
---- ---- ----
a b c
d e f
g h i
j (4 row(s) affected)
create table testf1(f1 char(1))
insert into testf1
select 'a' union all
select 'b'union all
select 'c'union all
select 'd'union all
select 'e'union all
select 'f'union all
select 'g'union all
select 'h'union all
select 'i'union all
select 'j';--sql 2005
;with cte as
(
select f1,rid=row_number() over (order by getdate())
from testf1
)select a.f1 as af,b.f1 as bf,c.f1 as cf
from (select *,(rid-1)/3 as fg from cte where (rid-1)%3=0) a
left join (select *,(rid-1)/3 as fg from cte where (rid-1)%3=1) b on a.fg = b.fg
left join (select *,(rid-1)/3 as fg from cte where (rid-1)%3=2) c on a.fg = c.fg--sql 2000select f1,rid=identity(int,1,1) into #testf1 from testf1select a.f1 as af,b.f1 as bf,c.f1 as cf
from (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=0) a
left join (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=1) b on a.fg = b.fg
left join (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=2) c on a.fg = c.fgdrop table #testf1
drop table testf1/*****************************(10 行受影响)
af bf cf
---- ---- ----
a b c
d e f
g h i
j NULL NULL(4 行受影响)(10 行受影响)af bf cf
---- ---- ----
a b c
d e f
g h i
j NULL NULL(4 行受影响)
DECLARE @A TABLE([F1] VARCHAR(1))
INSERT @A
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F' UNION ALL
SELECT 'G' UNION ALL
SELECT 'H' UNION ALL
SELECT 'I' UNION ALL
SELECT 'J';WITH M1 AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS ID,* FROM @A
),M2 AS
(
SELECT
ID%3 AS RID,
ROW_NUMBER () OVER (PARTITION BY ID%3 ORDER BY ID) AS NID,
* FROM M1
)
SELECT
MAX(CASE WHEN RID=1 THEN F1 ELSE '' END) AS F1,
MAX(CASE WHEN RID=2 THEN F1 ELSE '' END) AS F2,
MAX(CASE WHEN RID=0 THEN F1 ELSE '' END) AS F3
FROM M2 GROUP BY NID
/*
F1 F2 F3
---- ---- ----
A B C
D E F
G H I
J
*/