Oracle Within Group
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 USING (member_id) GROUP BY m.account_number , c.last_name ORDER BY c.last_name; |
It produces the following output:
ACCOUNT LAST_NAME NAMES MEMBERS ---------- ---------- ------------------------------------------ ------- SLC-000021 Jonah Gretelz S. 1 SLC-000020 Moss Jane W. 1 SLC-000023 Nathan Smith B. 1 SLC-000024 Potter Albus S., Ginny, Harry, James S., Lily L. 5 SLC-000022 Royal Jennifer E. 1 SJC-000003 Sweeney Ian M., Matthew, Meaghan 3 SJC-000002 Vizquel Doreen, Oscar 2 SLC-000018 Ward Clinton G. 1 SLC-000019 Ward Brandt H. 1 SJC-000001 Winn Brian, Randi 2 10 rows selected. |
I also found some existing examples you might like, at Oracle-Base, and there they’re showing you how to make it work in prior releases of the database without the new LISTAGG function.
Let’s say you just wanted a concatenated list of users, you could use the following in-line view approach:
1 2 3 4 5 | SELECT list.names FROM (SELECT 'List' AS list , LISTAGG(last_name ||', '||first_name||DECODE(middle_name,NULL,NULL,' '||SUBSTR(middle_name,1,1)||'.'),'; ') WITHIN GROUP (ORDER BY 2) AS names FROM contact) list; |
As always, I hope this helps somebody.
Subscribe via RSS