MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle Within Group

without comments

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.

Written by maclochlainn

April 7th, 2012 at 3:49 pm

Leave a Reply