WM_CONCAT? Ab 11gR2 dann doch lieber LISTAGG

Ausgangssituation:

Als SQL-Programmierer steht man häufig vor der Aufgabenstellung Werte aus mehreren Spalten in einer einzelnen Spalte darzustellen. Als Beispiel soll hier mal das bekannte Oracle HR-Schema mit der Tabelle Employees herhalten.

Ein einfacher Select auf die Tabelle liefert die Mitarbeiter mit ihrer Department-ID

select e.department_id, e.last_name from employees e;

DEPARTMENT_ID LAST_NAME
------------- --------------------------------------------------------------------------------
           90 King
           90 Kochhar
           90 De Haan
           60 Hunold
           60 Ernst
           60 Austin
           60 Pataballa
           60 Lorentz
          100 Greenberg
          100 Faviet
          100 Chen
          100 Sciarra
          100 Urman
          100 Popp
           30 Raphaely
           30 Khoo
           30 Baida
           30 Tobias
           30 Himuro
           30 Colmenares

……………………………….

Ist die jetzt die Aufgabenstellung zu jedem Department die Mitarbeiter in einer Spalte darzustellen wir sehr häufig die nicht dokumentierte Oracle-Funktion wm_concat benutzt. Mit dieser Funktion lässt sich folgendes Ergebnis erzielen:

select e.department_id, wm_concat(e.last_name) name from employees e
group by e.department_id

DEPARTMENT_ID NAME
------------- --------------------------------------------------------------------------------
           10 Whalen
           20 Hartstein,Fay
           30 Raphaely,Colmenares,Himuro,Tobias,Baida,Khoo
           40 Mavris
           50 Weiss,Grant,OConnell,Feeney,Walsh,Jones,McCain,Everett,Bell,Perkins,Gates,Dilly,
           60 Hunold,Lorentz,Pataballa,Austin,Ernst
           70 Baer
           80 Russell,Johnson,Livingston,Taylor,Hutton,Abel,Kumar,Bates,Smith,Fox,Bloom,Ozer,B
           90 King,De Haan,Kochhar
          100 Greenberg,Popp,Urman,Sciarra,Chen,Faviet
          110 Higgins,Gietz
                Grant

Das Problem hierbei ist allerdings, dass es sich um eine nicht dokumentierte Oracle-Funktion handelt und Oracle sich das Recht vorbehält Änderung an solchen Funktionen vorzunehmen ohne diese zu publizieren.

Genau dieses ist auch der Fall bei der Funktion wm_concat. Bis zu den Datenbank-Versionen 10.2.0.4 und 11.2.0.1 war der Rückgabe-Wert vom Typ varchar2. Ab den Versionen 10.2.0.5 und 11.2.0.2 ist es ein clob.

Die Auswirkungen bei einem Upgrade der Datenbank sollten den meisten bekannt sein. (invalide Views,….).

Hier die passende Oracle  Support Eintrag:

WM_CONCAT- Oracle Support

 

Lösung:

Um diese Problematik zu umgehen, empfiehlt es sich ab der Datenbank Version 11gR2 die analytische Funktion LISTAGG einzusetzen. Sie bietet nicht nur den kompletten Funktionsumfang von WM_CONCAT, sondern ist auch wesentlich schneller.

 

SELECT e.department_id, LISTAGG(e.last_name, ',') WITHIN GROUP (ORDER BY e.last_name) emps
FROM   employees e
GROUP BY e.department_id;

 

DEPARTMENT_ID EMPS
------------- --------------------------------------------------------------------------------
           10 Whalen
           20 Fay,Hartstein
           30 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
           40 Mavris
           50 Atkinson,Bell,Bissot,Bull,Cabrio,Chung,Davies,Dellinger,Dilly,Everett,Feeney,Fle
           60 Austin,Ernst,Hunold,Lorentz,Pataballa
           70 Baer
           80 Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Cambrault,Doran,Errazuriz,Fox,Gr
           90 De Haan,King,Kochhar
          100 Chen,Faviet,Greenberg,Popp,Sciarra,Urman
          110 Gietz,Higgins
                Grant

 

Exkurs:

Erweitert man das SQL-Statement um ein PARTITION BY ist es sogar möglich Werte der Einzeldatensätze kombiniert mit den zusammengefassten Daten in einer Zeile darzustellen:

SELECT department_id Dept,
       hire_date Datum,
       last_name Name,
       LISTAGG(last_name, ', ')
           WITHIN GROUP (ORDER BY hire_date, last_name)
                   OVER (PARTITION BY department_id) as Emp_list
FROM employees
ORDER BY Dept, Datum, Name;

 

DEPT DATUM NAME EMP_LIST

----- ----------- -------------------------------------------------------------------------------- ---------------------------------------------------------------

10 17.09.1987 Whalen Whalen

20 17.02.1996 Hartstein Hartstein, Fay

20 17.08.1997 Fay Hartstein, Fay

30 07.12.1994 Raphaely Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares

30 18.05.1995 Khoo Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares

30 24.07.1997 Tobias Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares
.
.
.

50 17.10.1995 Rajs Kaufling, Ladwig, Rajs, Sarchand, Bell, Mallin, Weiss, Davies, Marlow, Bull, Eve

     24.05.1999 Grant Grant

107 rows selected