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[Read more...]