举个例子,有一张表 Table,只有一个字段 【名称】,数据有99行名称
1
2
3
。。
99我想实现,用一个 SQL,把它变成这个样子:1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
。
91 92 93 94 95
96 97 98 99按照5个一组,横着显示
1
2
3
。。
99我想实现,用一个 SQL,把它变成这个样子:1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
。
91 92 93 94 95
96 97 98 99按照5个一组,横着显示
select name,row_number() over(order by getdate()) rn into #test from tablenameselect name as col1,
(select name from #test where #test.rn=tt.rn+1) as col2,
(select name from #test where #test.rn=tt.rn+2) as col3,
(select name from #test where #test.rn=tt.rn+3) as col4,
(select name from #test where #test.rn=tt.rn+4) as col5
from #test tt
where tt.rn%5=0
select (case when col%5=1 then col end) col1,
(case when col%5=2 then col end) col2,
(case when col%5=3 then col end) col3,
(case when col%5=4 then col end) col4,
(case when col%5=0 then col end) col5
from tb
select max(case when col%5=1 then col end) col1,
max(case when col%5=2 then col end) col2,
max(case when col%5=3 then col end) col3,
max(case when col%5=4 then col end) col4,
max(case when col%5=0 then col end) col5
from tb
group by (col-1)/5
select name,row_number() over(order by getdate()) rn into #test from tablenameselect name as col1,
(select name from #test where #test.rn=tt.rn+1) as col2,
(select name from #test where #test.rn=tt.rn+2) as col3,
(select name from #test where #test.rn=tt.rn+3) as col4,
(select name from #test where #test.rn=tt.rn+4) as col5
from #test tt
where (tt.rn-1)%5=0
'row_number' 不是可以识别的 函数名。
declare @tb table(名称 int);
declare @i int;
set @i=1;
while @i<100
begin
insert into @tb values(@i);
set @i=@i+1;
end;
select
MAX(case when 名称 % 5= 1 then 名称 end) as col1
,MAX(case when 名称 % 5= 2 then 名称 end) as col2
,MAX(case when 名称 % 5= 3 then 名称 end) as col3
,MAX(case when 名称 % 5= 4 then 名称 end) as col4
,MAX(case when 名称 % 5= 0 then 名称 end) as col5
from @tb
group by (名称-1)/5/*结果
col1 col2 col3 col4 col5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
26 27 28 29 30
31 32 33 34 35
36 37 38 39 40
41 42 43 44 45
46 47 48 49 50
51 52 53 54 55
56 57 58 59 60
61 62 63 64 65
66 67 68 69 70
71 72 73 74 75
76 77 78 79 80
81 82 83 84 85
86 87 88 89 90
91 92 93 94 95
96 97 98 99 NULL
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-16 15:25:56
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15
--------------开始查询--------------------------
select
max(case when ID%5=1 then col end) col1,
max(case when ID%5=2 then col end) col2,
max(case when ID%5=3 then col end) col3,
max(case when ID%5=4 then col end) col4,
max(case when ID%5=0 then col end) col5
from
(select ID=ROW_NUMBER()over(order by getdate()),* from tb)t
group by
(ID-1)/5----------------结果----------------------------
/* col1 col2 col3 col4 col5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)*/
into #tb
from tbselect max(case when rid%5=1 then col end) col1,
max(case when rid%5=2 then col end) col2,
max(case when rid%5=3 then col end) col3,
max(case when rid%5=4 then col end) col4,
max(case when rid%5=0 then col end) col5
from #tb
group by (rid-1)/5drop table #tb
(select name from #test where #test.rn=tt.rn+1) as col2,
(select name from #test where #test.rn=tt.rn+2) as col3,
(select name from #test where #test.rn=tt.rn+3) as col4,
(select name from #test where #test.rn=tt.rn+4) as col5
from #test tt
where tt.rn%5=0
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-16 15:25:56
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15
--------------开始查询--------------------------
select
max(case when ID%5=1 then col end) col1,
max(case when ID%5=2 then col end) col2,
max(case when ID%5=3 then col end) col3,
max(case when ID%5=4 then col end) col4,
max(case when ID%5=0 then col end) col5
from
(select ID=ROW_NUMBER()over(order by getdate()),* from tb)t
group by
(ID-1)/5
---2000
select ID=IDENTITY(int,1,1),* into #t from tb
select
max(case when ID%5=1 then col end) col1,
max(case when ID%5=2 then col end) col2,
max(case when ID%5=3 then col end) col3,
max(case when ID%5=4 then col end) col4,
max(case when ID%5=0 then col end) col5
from
#t t
group by
(ID-1)/5
drop table #t----------------结果----------------------------
/* col1 col2 col3 col4 col5
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)*/
(select name from #test where #test.rn=tt.rn+1) as col2,
(select name from #test where #test.rn=tt.rn+2) as col3,
(select name from #test where #test.rn=tt.rn+3) as col4,
(select name from #test where #test.rn=tt.rn+4) as col5
from #test tt
where (tt.rn-1)%5=0