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:
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
