表 Aid name sortno001 aaa 1
002 bbb 2
003 ccc 3
004 ddd 4
005 eee 5
转为id1 name1 id2 name2 id3 name3
001 aaa 002 bbb 003 ccc
004 ddd 005 eee ...
...或者转为
id1 name1 id2 name2 id3 name3 id4 name4
002 bbb 2
003 ccc 3
004 ddd 4
005 eee 5
转为id1 name1 id2 name2 id3 name3
001 aaa 002 bbb 003 ccc
004 ddd 005 eee ...
...或者转为
id1 name1 id2 name2 id3 name3 id4 name4
id1=max(case when sortno%3=1 then id else 0 end),
name1=max(case when sortno%3=1 then name else '' end),
id2=max(case when sortno%3=2 then id else 0 end),
name2=max(case when sortno%3=2 then name else '' end),
id3=max(case when sortno%3=0 then id else 0 end),
name3=max(case when sortno%3=0 then name else '' end)
from A
;with ach as
(
select *,rid=row_number() over (order by getdate())
from a
)select
id1=max(case when rid%3=1 then id else 0 end),
name1=max(case when rid%3=1 then name else '' end),
id2=max(case when rid%3=2 then id else 0 end),
name2=max(case when rid%3=2 then name else '' end),
id3=max(case when rid%3=0 then id else 0 end),
name3=max(case when rid%3=0 then name else '' end)
from ach
;with ach as
(
select *,rid=row_number() over (order by getdate())
from a
)select
id1=max(case when rid%3=1 then id else 0 end),
name1=max(case when rid%3=1 then name else '' end),
id2=max(case when rid%3=2 then id else 0 end),
name2=max(case when rid%3=2 then name else '' end),
id3=max(case when rid%3=0 then id else 0 end),
name3=max(case when rid%3=0 then name else '' end)
from ach
group by (rid-1)/3+1
select
id1=max(case when sortno%3=1 then id else 0 end),
name1=max(case when sortno%3=1 then name else '' end),
id2=max(case when sortno%3=2 then id else 0 end),
name2=max(case when sortno%3=2 then name else '' end),
id3=max(case when sortno%3=0 then id else 0 end),
name3=max(case when sortno%3=0 then name else '' end)
from A
group by (sortno-1)/3+1
id1=max(case when sortno%3=1 then id else 0 end),
name1=max(case when sortno%3=1 then name else '' end),
id2=max(case when sortno%3=2 then id else 0 end),
name2=max(case when sortno%3=2 then name else '' end),
id3=max(case when sortno%3=0 then id else 0 end),
name3=max(case when sortno%3=0 then name else '' end)
from A
group by (sortno-1)/3
declare @表A table (id varchar(3),name varchar(3),sortno int)
insert into @表A
select '001','aaa',1 union all
select '002','bbb',2 union all
select '003','ccc',3 union all
select '004','ddd',4 union all
select '005','eee',5 union all
select '006','fff',6;with m1 as(select row_number () over (order by id) as rid,* from @表A where (id+0)%3=0)
,m2 as(select row_number () over (order by id) as rid,* from @表A where (id+0)%3=1)
,m3 as (select row_number () over (order by id) as rid,* from @表A where (id+0)%3=2)select b.id,b.name,c.id,c.name,a.id,a.name from m1 a
left join m2 b on a.rid=b.rid left join m3 c on a.rid=c.rid
/*
id name id name id name
---- ---- ---- ---- ---- ----
001 aaa 002 bbb 003 ccc
004 ddd 005 eee 006 fff
*/
得出来的数据是如下的
id name id1 name1 id2 name2
1 Account 0 0
0 2 Adm 0
0 0 3 QA
4 Department 0 0
0 5 CCDSH 0
0 0 6 LEGAL
7 PROJ 0 0
declare @表A table (id varchar(3),name varchar(3),sortno int)
insert into @表A
select '001','aaa',1 union all
select '002','bbb',2 union all
select '003','ccc',3 union all
select '004','ddd',4 union all
select '005','eee',5 union all
select '006','fff',6select
id1=max(case when sortno%3=1 then id else 0 end),
name1=max(case when sortno%3=1 then name else '' end),
id2=max(case when sortno%3=2 then id else 0 end),
name2=max(case when sortno%3=2 then name else '' end),
id3=max(case when sortno%3=0 then id else 0 end),
name3=max(case when sortno%3=0 then name else '' end)
from @表A
group by (sortno-1)/3/***************id1 name1 id2 name2 id3 name3
----------- ----- ----------- ----- ----------- -----
1 aaa 2 bbb 3 ccc
4 ddd 5 eee 6 fff(2 行受影响)
楼主无视我啊!
declare @表A table (id varchar(3),name varchar(3),sortno int)
insert into @表A
select '001','aaa',1 union all
select '002','bbb',2 union all
select '003','ccc',3 union all
select '004','ddd',4 union all
select '005','eee',5 union all
select '006','fff',6;with ach as
(
select *,rid=row_number() over (order by getdate())
from @表A
)select
id1=max(case when rid%3=1 then id else 0 end),
name1=max(case when rid%3=1 then name else '' end),
id2=max(case when rid%3=2 then id else 0 end),
name2=max(case when rid%3=2 then name else '' end),
id3=max(case when rid%3=0 then id else 0 end),
name3=max(case when rid%3=0 then name else '' end)
from ach
group by (rid-1)/3+1
/**************id1 name1 id2 name2 id3 name3
----------- ----- ----------- ----- ----------- -----
1 aaa 2 bbb 3 ccc
4 ddd 5 eee 6 fff(2 行受影响)
declare @表A table (id varchar(3),name varchar(3),sortno int)
insert into @表A
select '001','aaa',1 union all
select '002','bbb',2 union all
select '003','ccc',3 union all
select '004','ddd',4 union all
select '005','eee',5 union all
select '006','fff',6select *,rid=identity(int,1,1)
into #tb
from @表Aselect
id1=max(case when rid%3=1 then id else 0 end),
name1=max(case when rid%3=1 then name else '' end),
id2=max(case when rid%3=2 then id else 0 end),
name2=max(case when rid%3=2 then name else '' end),
id3=max(case when rid%3=0 then id else 0 end),
name3=max(case when rid%3=0 then name else '' end)
from #tb
group by (rid-1)/3+1drop table #tb/***************id1 name1 id2 name2 id3 name3
----------- ----- ----------- ----- ----------- -----
1 aaa 2 bbb 3 ccc
4 ddd 5 eee 6 fff(2 行受影响)