Analytische Funktionen (Teil 2): LISTAGG mit eindeutiger Liste

Von Tobias Arnhold 10.01.2015
Die LISTAGG Funktion dient der Generierung von zusammenkonkatenierten Strings auf Basis einer Spalte. Wenn in der Spalte ein Wert mehrfach vorkommt, dann wird die Liste ebenfalls mit doppelten Werten generiert.

Im folgenden demonstriere ich ein Beispiel um dieses Problem zu lösen.

Beispieldaten - Land und Ort:
SELECT
 LAND, ORT
FROM STADT_LISTE 
ORDER BY 1,2;
Ergebnis:
LAND ORT
Belgien Bruxelles
Belgien Bruxelles
Polen Katowice
Polen Wegliniec
Schweiz Basel
Schweiz Bern
Schweiz Riehen
Schweiz Thayngen
Schweiz Thayngen

Ziel ist es nun, nur die Länder mit jeweils einer Liste an Orten auszugeben. Unglücklicherweise hat meine Tabelle die Orte Bruxelles und Thayngen doppelt hinterlegt. Mit Hilfe der ROW_NUMBER() Funktion selektiere ich diese Werte vor der LISTAGG-Funktion heraus.
SELECT
  LAND,
  REPLACE(':'||LISTAGG(ORT_UNGUELTIG,':')  WITHIN GROUP (ORDER BY ORT_UNGUELTIG)||':','::',NULL) AS ORT_LISTE_UNGUELTIG,
  REPLACE(':'||LISTAGG(ORT_GUELTIG,':')  WITHIN GROUP (ORDER BY ORT_GUELTIG)||':','::',NULL) AS ORT_LISTE_GUELTIG
FROM (
  SELECT  
   LAND, 
   ORT AS ORT_UNGUELTIG,
   CASE WHEN 
         ROW_NUMBER() OVER (PARTITION BY LAND, ORT ORDER BY ORT) = 1
         THEN ORT
         ELSE NULL
    END AS ORT_GUELTIG
  FROM STADT_LISTE 
)
GROUP BY LAND
ORDER BY LAND;
Ergebnis:
LAND ORT_LISTE_UNGUELTIG ORT_LISTE_GUELTIG
Belgien :Bruxelles:Bruxelles: :Bruxelles:
Polen :Katowice:Wegliniec: :Katowice:Wegliniec:
Schweiz :Basel:Bern:Riehen:Thayngen:Thayngen: :Basel:Bern:Riehen:Thayngen:

Weiterführende Infos zum Thema LISTAGG und Analytische Funktionen findet Ihr hier: Die besten HowTo's rund um fortgeschrittene SQL-Techniken

No Comment to " Analytische Funktionen (Teil 2): LISTAGG mit eindeutiger Liste "