Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
COLUMN list FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names FORMAT A42
COLUMN members FORMAT 9,990
SELECT m.account_number AS account
, c.last_name AS last_name
, LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
WITHIN GROUP (ORDER BY 2) AS names
, COUNT(*) AS members
FROM contact c INNER JOIN member m … |