select max(decode(mod(rn-1,5),0,a)), max(decode(mod(rn-1,5),1,a)), max(decode(mod(rn-1,5),2,a)), max(decode(mod(rn-1,5),3,a)), max(decode(mod(rn-1,5),4,a)), from( select a,row_number()over(order by a)rn from tt) group by trunc((rn-1)/5)
/* Formatted on 2009/12/22 17:47 (Formatter Plus v4.8.8) */ WITH a AS (SELECT 'a' letter FROM DUAL UNION ALL SELECT 'b' letter FROM DUAL UNION ALL SELECT 'c' letter FROM DUAL UNION ALL SELECT 'd' letter FROM DUAL UNION ALL SELECT 'e' letter FROM DUAL UNION ALL SELECT 'f' letter FROM DUAL UNION ALL SELECT 'g' letter FROM DUAL UNION ALL SELECT 'h' letter FROM DUAL UNION ALL SELECT 'i' letter FROM DUAL UNION ALL SELECT 'j' letter FROM DUAL UNION ALL SELECT 'k' letter FROM DUAL UNION ALL SELECT 'l' letter FROM DUAL UNION ALL SELECT 'm' letter FROM DUAL UNION ALL SELECT 'n' letter FROM DUAL UNION ALL SELECT 'o' letter FROM DUAL UNION ALL SELECT 'p' letter FROM DUAL UNION ALL SELECT 'q' letter FROM DUAL UNION ALL SELECT 'r' letter FROM DUAL UNION ALL SELECT 's' letter FROM DUAL UNION ALL SELECT 't' letter FROM DUAL UNION ALL SELECT 'u' letter FROM DUAL UNION ALL SELECT 'v' letter FROM DUAL UNION ALL SELECT 'w' letter FROM DUAL UNION ALL SELECT 'x' letter FROM DUAL UNION ALL SELECT 'y' letter FROM DUAL UNION ALL SELECT 'z' letter FROM DUAL) SELECT wmsys.wm_concat (UPPER (aa.letter)) 字段1 FROM (SELECT * FROM a ORDER BY letter) aa GROUP BY TRUNC ((ROWNUM - 1) / 6)字段1A,B,C,E,F,D G,H,J,K,L,I M,R,N,O,Q,P S,X,W,T,U,V Y,Z
如果你是想要这样的结果 字段1 字段2 字段3 字段4 字段5 A B C D E F G H.... X Y 那可以参照狂浪的写法 如果是一个字段的,可以参考我的语句 SELECT wmsys.wm_concat (UPPER (aa.letter)) 字段1 FROM (SELECT * FROM a ORDER BY letter) aa GROUP BY TRUNC ((ROWNUM - 1) / 5)
max(decode(mod(rn-1,5),1,a)),
max(decode(mod(rn-1,5),2,a)),
max(decode(mod(rn-1,5),3,a)),
max(decode(mod(rn-1,5),4,a)),
from(
select a,row_number()over(order by a)rn from tt)
group by trunc((rn-1)/5)
/* Formatted on 2009/12/22 17:47 (Formatter Plus v4.8.8) */
WITH a AS
(SELECT 'a' letter
FROM DUAL
UNION ALL
SELECT 'b' letter
FROM DUAL
UNION ALL
SELECT 'c' letter
FROM DUAL
UNION ALL
SELECT 'd' letter
FROM DUAL
UNION ALL
SELECT 'e' letter
FROM DUAL
UNION ALL
SELECT 'f' letter
FROM DUAL
UNION ALL
SELECT 'g' letter
FROM DUAL
UNION ALL
SELECT 'h' letter
FROM DUAL
UNION ALL
SELECT 'i' letter
FROM DUAL
UNION ALL
SELECT 'j' letter
FROM DUAL
UNION ALL
SELECT 'k' letter
FROM DUAL
UNION ALL
SELECT 'l' letter
FROM DUAL
UNION ALL
SELECT 'm' letter
FROM DUAL
UNION ALL
SELECT 'n' letter
FROM DUAL
UNION ALL
SELECT 'o' letter
FROM DUAL
UNION ALL
SELECT 'p' letter
FROM DUAL
UNION ALL
SELECT 'q' letter
FROM DUAL
UNION ALL
SELECT 'r' letter
FROM DUAL
UNION ALL
SELECT 's' letter
FROM DUAL
UNION ALL
SELECT 't' letter
FROM DUAL
UNION ALL
SELECT 'u' letter
FROM DUAL
UNION ALL
SELECT 'v' letter
FROM DUAL
UNION ALL
SELECT 'w' letter
FROM DUAL
UNION ALL
SELECT 'x' letter
FROM DUAL
UNION ALL
SELECT 'y' letter
FROM DUAL
UNION ALL
SELECT 'z' letter
FROM DUAL)
SELECT wmsys.wm_concat (UPPER (aa.letter)) 字段1
FROM (SELECT *
FROM a
ORDER BY letter) aa
GROUP BY TRUNC ((ROWNUM - 1) / 6)字段1A,B,C,E,F,D
G,H,J,K,L,I
M,R,N,O,Q,P
S,X,W,T,U,V
Y,Z
字段1 字段2 字段3 字段4 字段5
A B C D E
F G H....
X Y
那可以参照狂浪的写法
如果是一个字段的,可以参考我的语句
SELECT wmsys.wm_concat (UPPER (aa.letter)) 字段1
FROM (SELECT *
FROM a
ORDER BY letter) aa
GROUP BY TRUNC ((ROWNUM - 1) / 5)