使用基于 row_number()
分组的条件聚合,并使用 %
取模 进行列定位:
测试设置:
select n into dbo.numbers from (values
(1), (2), (3), (4), (5), (6), (7), (8), (9),(10)
,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
) t(n)
delete from dbo.numbers where n in (12,13,17);
查询:
select
col1 = sum(case when rn%5=0 then n end)
, col2 = sum(case when rn%5=1 then n end)
, col3 = sum(case when rn%5=2 then n end)
, col4 = sum(case when rn%5=3 then n end)
, col5 = sum(case when rn%5=4 then n end)
from (
select n, rn = row_number() over (order by n)-1
from dbo.numbers
) t
group by rn/5;
rextester演示:http://rextester.com/UHKY16981
返回:
+
| col1 | col2 | col3 | col4 | col5 |
+
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
| 11 | 14 | 15 | 16 | 18 |
| 19 | 20 | NULL | NULL | NULL |
+
使用
pivot()
而不是条件聚合的相同概念返回相同的结果。
select
col1 = [0]
, col2 = [1]
, col3 = [2]
, col4 = [3]
, col5 = [4]
from (
select n
, rn = (row_number() over (order by n)-1)%5
, grp = (row_number() over (order by n)-1)/5
from dbo.numbers
) t
pivot (sum(n) for rn in ([0],[1],[2],[3],[4])) p;