String aggregation in Oracle : Emulate GROUP_CONCAT

The following article will be written in german.
Why ? No reason at all, i just hope that there are german people who are interested in SQL queries...

Na dann, los geht's.
Das heutige Thema handelt ein rekurrenter Bedürfnis :
Wie kann man Strings in einer grouppierung konkatinieren ?
Zum Beispliel, nehmen wir an, es gibt eine Tabelle der Fussballmannschaftenspieler : jede Zeile steht also für einen Spieler.
Team_mitglieder (Club, Spielername, Nummer)
Aus irgend einenem Grund, soll für jede Mannschaft eine einzige Zeile alle Namen der Mitglieder auslisten (getrennt mit komma zum Beispiel).

MySQL bietet die Funktion GROUP_CONCAT an. Und dadurch ist die Query ganz einfach :

SELECT Club, GROUP_CONCAT(Spielername, ',') ORDER BY Nummer
FROM Team_mitglieder
GROUP BY Club

MySQL ist leider das einzige DBMS, das diese Funktion direkt anbietet.
Im folgendem Artikel werden desshalb zwei verschiedene Wege (eigentlich gibt es noch einige anderen) präsentiert, das selbe in Oracle zu erreichen :
- durch die Verwendung der Baumstrukture Funktionen (CONNECT BY)
- mit den Oracle XML Funktionen

1) CONNECT BY Methode

Ihr wisst bestimmt schon, wofür mann dies gebrauchen kann :
Stellt euch vor, die Hierarchie in euren Firma wird von der Folgende Tabelle verkörpert :
CREATE TABLE Angestellte (
Id NUMBER,
Name VARCHAR(20),
LeiterId NUMBER) ;

-- die Oberleiterin
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (1, 'Angela', NULL);
-- Angela's erster Sklave
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (2, 'Franz', 1);
-- Angela's zweiter Sklave
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (3, 'Ulrich', 1);
-- Ulrich's Helfer
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (4, 'Klöden', 3);
-- Und heidi klum, nur so...
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (5, 'Heidi', 3);

Versuchen wir mal jetzt die ganze Hierarchie zu bilden :

SELECT path
FROM (
SELECT sys_connect_by_path(name, '->') AS path, CONNECT_BY_ISLEAF AS flg, CONNECT_BY_ROOT id rid
FROM Angestellte
CONNECT BY PRIOR id = leiterid
)
WHERE rid = 1
 
PATH
--------------------------------
->Angela
->Angela->Franz
->Angela->Ulrich
->Angela->Ulrich->Klöden
->Angela->Ulrich->Heidi

Jedes Wert wird rekursiv mit dem Vorgängern verknüpft, und die ganze Kette steht zur verfügung durch die Funktion SYS_CONNECT_BY_PATH.

Zurück zum Hauptthema : man braucht nur eine Einkantenbaumstruktur bestimmen, dessen Knoten die Spielernamen sind.
Erste Stufe : Nummerierung der Spieler
Zweite Stufe : Bildung des Baumes

CREATE TABLE test AS
SELECT    'Bayern' as team, 'Kahn' as name FROM DUAL UNION ALL
SELECT    'Bayern','Lucio'        FROM DUAL UNION ALL
SELECT    'Bayern','Traore'        FROM DUAL UNION ALL
SELECT    'Bayern','Philipp Lahm'        FROM DUAL UNION ALL
SELECT    'Bayern','Valerien Ismael'    FROM DUAL UNION ALL
SELECT    'Bayern','Michael Ballack'    FROM DUAL UNION ALL
SELECT    'Bayern','Jens Jeremies'    FROM DUAL UNION ALL
SELECT    'Bayern','Andreas Ottl'        FROM DUAL UNION ALL
SELECT    'Bayern','Johan Micoud'        FROM DUAL UNION ALL
SELECT    'Bayern','Ronaldo'        FROM DUAL UNION ALL
SELECT    'Bayern','Guerrero'        FROM DUAL UNION ALL
SELECT    'Bayern','Robbie Fowler'    FROM DUAL UNION ALL
SELECT    'Dortmund','Roman Weidenfeller'    FROM DUAL UNION ALL
SELECT    'Dortmund','Ashley Cole'    FROM DUAL UNION ALL
SELECT    'Dortmund','John Terry'        FROM DUAL UNION ALL
SELECT    'Dortmund','Ricardo Carvalho'    FROM DUAL UNION ALL
SELECT    'Dortmund','Phillip Lahm'    FROM DUAL UNION ALL
SELECT    'Dortmund','Sergio Ramos'    FROM DUAL UNION ALL
SELECT    'Dortmund','Patrick Vieira'    FROM DUAL UNION ALL
SELECT    'Dortmund','Bastian Schweinsteiger'    FROM DUAL UNION ALL
SELECT    'Dortmund','Steven Gerrard'    FROM DUAL UNION ALL
SELECT    'Dortmund','Cristiano Ronaldo'    FROM DUAL UNION ALL
SELECT    'Dortmund','Thierry Henry' FROM DUAL


SELECT  team, ltrim(path, ',') as path
FROM (
   SELECT team, SYS_CONNECT_BY_PATH(name, ',') as path, CONNECT_BY_ISLEAF flg
   FROM (
           SELECT team, name, ROW_NUMBER() OVER(PARTITION BY team ORDER BY NULL) rk
           FROM test
   )
   CONNECT BY PRIOR rk = rk - 1 AND PRIOR team = team
   START WITH rk = 1
)
WHERE flg = 1

TEAM
--------
PATH
------------------------------------------------------------------------------------------------------------------------------------
Bayern
Kahn,Lucio,Robbie Fowler,Guerrero,Ronaldo,Johan Micoud,Andreas Ottl,Jens Jeremies,Michael Ballack,Valerien Ismael,Philipp Lahm,Traor
e

Dortmund
Cristiano Ronaldo,Steven Gerrard,Bastian Schweinsteiger,Patrick Vieira,Sergio Ramos,Thierry Henry,Ricardo Carvalho,John Terry,Ashley
 Cole,Roman Weidenfeller,Phillip Lahm

- ROW_NUMBER() nummeriert die Zeilen in Jeder Gruppe definiert in der PARTITION BY Klause
- CONNECT_BY_LEAF ergibt 1, wenn das Wert ein Endknoten ist


2) XML Methode :

Seit Oracle 9i gibt es den Datentyp XMLTYpe und verschiedene passende Funktionen.
Unter anderen wertvollen Möglichkeiten, kann man :
- XML Elemente schaffen : XMLElem(Wert, Knotenname)
- Eine Gruppe XML Elemente aggregieren : XMLAgg(Element ORDER BY Reihenfolge)

Natürlich muss man danach die XML Markierungen reinigen... aber trotzdem bleibt das ganze ziemlich einfach :
SELECT team, RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", name )), '<x>', ' '), '</x>', ','), ',') as Agg
FROM test
GROUP BY team

TEAM
--------
AGG
------------------------------------------------------------------------------------------------------------------------------------
Bayern
 Kahn, Lucio, Traore, Valerien Ismael, Jens Jeremies, Johan Micoud, Guerrero, Robbie Fowler, Ronaldo, Andreas Ottl, Michael Ballack,
 Philipp Lahm

Dortmund
 Roman Weidenfeller, Sergio Ramos, Bastian Schweinsteiger, Cristiano Ronaldo, Thierry Henry, Steven Gerrard, Patrick Vieira, Phillip
 Lahm, Ashley Cole, John Terry, Ricardo Carvalho

Diese XML Methode finde ich intuitiver als die Baummethode.
Ausserdem mag sie aus dem sichtpunkt der Leistungen viel besser abschneiden, wenigstens bevor 10g.

Das war's !

Comments

[Laurent Schneider]

LISTAGG gibt es auch erst ab 11.2 ;-)

[Stefan]

die xml methode scheint mir viel einfacher. werde ich gleich mal testen. Auf jedem fall super erklärt.

[Pacman]

Danke Stefan, hat mich sehr gefreut dass ein deutscher (italiener ?) mein Blog kommentiert :D


XMLAGG ist auch meine Lieblingsmethode bevor 11g.


Falls du 11g benutzt, gibt's die passende built-in Funktion LISTAGG :


http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm

[Waldar]

Je t'invite à écrire une version anglaise ou française de ton billet ;)

[Pacman]

En fait, je me suis fixé une ligne de conduite :
- En français pour les trucs rigolos, où que je trouve important de soulever
- En anglais quand c'est un truc qui me laisse perplexe et sur lequel j'espère qu'un illuminé pourrait répondre :)
- En allemand de temps en temps pour re-traiter un sujet déjà traité 1000 fois sur d'autres sites, juste pour l'entraînement linguistique ^^
(Je pense qu'il y a suffisament de sources bien plus claires que moi pour traiter l'aggrégation de chaînes de caractères :))

[Waldar]

Heureusement que je connaissais déjà ces fonctions ;)

[Pacman]

En plus, je sens que vu comme c'est écrit, même un allemand n'arriverait pas à comprendre ce que j'essayais de dire :)