# 一个列传行的问题

v_id        v_log
a        1
a        2
a        3
a        4
a        5
a        6
a        7
a        8
a        9
a        10
a        11
a        12
b        1
b        2
b        3
b        4
b        5
b        6

a  1,2,3,4,5
a  6,7,8,9,10
a  11,12
b  1,2,3,4,5
b  6

SELECT all_lv_log,
LEVEL,
substr(all_lv_log,
regexp_instr(all_lv_log, ',', 1, 5 * (LEVEL - 1) + 1) - 1,
decode(regexp_instr(all_lv_log, ',', 1, 5 * LEVEL),
0,
length(all_lv_log),
(regexp_instr(all_lv_log, ',', 1, 5 * LEVEL) - 1) -
(regexp_instr(all_lv_log, ',', 1, 5 * (LEVEL - 1) + 1) - 1) + 1))
FROM (SELECT *
FROM (SELECT listagg(lv_log, ',') within GROUP(ORDER BY lv_log) over(PARTITION BY v_id) all_lv_log,
lv_log,
v_id,
cnt,
cnt2,
rn
FROM (SELECT COUNT(*) over(PARTITION BY v_id) cnt,
ceil((COUNT(*) over(PARTITION BY v_id)) / 5) cnt2,
row_number() over(PARTITION BY v_id ORDER BY lv_log) rn,
lv_log,
v_id
FROM t))
WHERE rn = 1)
CONNECT BY PRIOR v_id = v_id
AND LEVEL <= cnt2
AND PRIOR dbms_random.value IS NOT NULL;

udfrog的答案:

select  v_id, listagg(v_log, ',') within group (order by rn) result
from    (
select  t.*, row_number() over (partition by v_id order by v_log) rn
from    t
)
group by v_id, trunc((rn-1)/5);

• 博文量
559
• 访问量
854605