用一个实例讲解GROUP BY CEIL的使用方法

SQL> WITH A AS (SELECT "A" CD FROM DUAL
2 UNION
3 SELECT "B" CD FROM DUAL
4 UNION
5 SELECT "C" CD FROM DUAL
6 UNION
7 SELECT "D" CD FROM DUAL
8 UNION
9 SELECT "E" CD FROM DUAL
10 UNION
11 SELECT "F" CD FROM DUAL
12 UNION
13 SELECT "G" CD FROM DUAL
14 UNION
15 SELECT "H" CD FROM DUAL
16 UNION
17 SELECT "I" CD FROM DUAL
18 )
19 select max(decode(mod(rownum, 5), 1, CD, null)) ID1,
20 max(decode(mod(rownum, 5), 2, CD, null)) ID2,
21 max(decode(mod(rownum, 5), 3, CD, null)) ID3,
22 max(decode(mod(rownum, 5), 4, CD, null)) ID4,
23 max(decode(mod(rownum, 5), 0, CD, null)) ID5
24 from a
25 group by ceil(rownum / 5)
26 ;

ID1 ID2 ID3 ID4 ID5
--- --- --- --- ---
A B C D E
F G H I 


例二：

with a as (select "01" ym from dual
union
select "02" ym from dual
union
select "03" ym from dual
union
select "04" ym from dual
union
select "05" ym from dual
union
select "06" ym from dual
union
select "07" ym from dual
union
select "08" ym from dual
union
select "09" ym from dual
union
select "10" ym from dual
union
select "11" ym from dual
union
select "12" ym from dual
)
select 
max(decode(mod(rownum, 6), 1, ym, null)) ID1,
max(decode(mod(rownum, 6), 2, ym, null)) ID2,
max(decode(mod(rownum, 6), 3, ym, null)) ID3,
max(decode(mod(rownum, 6), 4, ym, null)) ID4,
max(decode(mod(rownum, 6), 5, ym, null)) ID5,
max(decode(mod(rownum, 6), 0, ym, null)) ID6
from a
group by ceil(rownum / 6)

ID1 ID2 ID3 ID4 ID5 ID6
--- --- --- --- --- ---
01 02 03 04 05 06
07 08 09 10 11 12 数据挖掘工具