1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
COLUMN nbr FORMAT 99999;
COLUMN letters FORMAT a30;
WITH testtable AS
(
SELECT 'A' AS letter, 1 AS nbr FROM DUAL UNION
SELECT 'B' AS letter, 2 AS nbr FROM DUAL UNION
SELECT 'C' AS letter, 1 AS nbr FROM DUAL UNION
SELECT 'D' AS letter, 3 AS nbr FROM DUAL UNION
SELECT 'E' AS letter, 4 AS nbr FROM DUAL UNION
SELECT 'F' AS letter, 4 AS nbr FROM DUAL UNION
SELECT 'G' AS letter, 4 AS nbr FROM DUAL UNION
SELECT 'H' AS letter, 5 AS nbr FROM DUAL UNION
SELECT 'I' AS letter, 5 AS nbr FROM DUAL UNION
SELECT 'J' AS letter, 1 AS nbr FROM DUAL UNION
SELECT 'K' AS letter, 1 AS nbr FROM DUAL
)
SELECT nbr AS nbr,
LISTAGG(letter, ', ') WITHIN GROUP (ORDER BY letter ASC) AS letters
FROM testtable
GROUP BY nbr
ORDER BY nbr ASC; |
Partager