By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (
key1 NUMBER,
key2 NUMBER,
foo VARCHAR2(20),
bar VARCHAR2(20)
)
INSERT INTO table_name (key1, key2, foo, bar)
select 1, 2, '1.0', 'A' from dual UNION ALL
select 1, 2, '2.0', 'A' from dual UNION ALL
select 3, 4, '2.0', 'A' from dual UNION ALL
select 3, 4, '2.0', 'B' from dual UNION ALL
select 3, 4, '2.0', 'B' from dual;
5 rows affected
SELECT key1,
key2,
JSON_ARRAYAGG(foo) foo,
JSON_ARRAYAGG(bar) bar
FROM table_name
GROUP BY key1, key2
| KEY1 | KEY2 | FOO | BAR |
|---|---|---|---|
| 1 | 2 | ["1.0","2.0"] | ["A","A"] |
| 3 | 4 | ["2.0","2.0","2.0"] | ["A","B","B"] |
SELECT key1,
key2,
( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
FROM TABLE(t.foos) ) AS foo,
( SELECT JSON_ARRAYAGG(COLUMN_VALUE)
FROM TABLE(t.bars) ) AS bar
FROM (
SELECT key1,
key2,
CAST(COLLECT(DISTINCT foo) AS SYS.ODCIVARCHAR2LIST) AS foos,
CAST(COLLECT(DISTINCT bar) AS SYS.ODCIVARCHAR2LIST) AS bars
FROM table_name
GROUP BY key1, key2
) t
| KEY1 | KEY2 | FOO | BAR |
|---|---|---|---|
| 1 | 2 | ["1.0","2.0"] | ["A"] |
| 3 | 4 | ["2.0"] | ["A","B"] |