Was ist STRING_AGG?
In der letzten Woche hattet ihr gesehen, wie ihr mit STUFF
und SELECT FOR XML
eine kommaseparierte Liste aus den Zeilen einer Tabelle erzeugen könnt. Seit dem SQL Server 2017 ist das mit der Einführung der STRING_AGG
-Funktion noch einfacher geworden. Die STRING_AGG
-Funktion konkateniert Zeichenketten und fügt ein Trennzeichen ein.
Was könnt ihr mit STRING_AGG so machen?
Erinnern wir uns an vergangene Woche, als wir eine kommaseparierte Liste von Transaktionstypen mit folgendem Statement erzeugt:
SELECT STUFF( (SELECT ','+TransactionTypeName FROM [Application].[TransactionTypes] FOR XML PATH ('') ), 1, 1, '')
Das ist nicht unbedingt elegant, funktioniert aber auf allen Versionen des SQL Servers. Unschön daran ist, dass wir SELECT
-Statements für dieses Vorgehen verschachteln müssen, wenn wir andere Spalten hinzufügen möchten.
Eleganter ist da die Verwendung von STRING_AGG
, denn hier ist kein geschachteltes SELECT
mehr nötig, die Abfrage ist ganz einfach und direkt:
SELECT STRING_AGG(TransactionTypeName, ', ') FROM [Application].[TransactionTypes]
Diese Abfrage ist natürlich viel eleganter und einfacher als die Abfrage der letzten Woche. Das ist natürlich ein großer Vorteil dieser Funktion. Doch noch spannender wird es, wenn wir nun eine weitere Spalte, die TransactionTypeID
mit in die Abfrage aufnehmen möchten. Bei der STUFF
-Abfrage greift dann die Tatsache, dass STUFF
eine Aggregats-Funktion wie SUM
, MIN
oder MAX
ist, weshalb wir ein GROUP BY
benötigen. Die Abfrage wird dadurch zu:
SELECT TransactionTypeID ,STUFF( (SELECT ','+TransactionTypeName FROM [Application].[TransactionTypes] FOR XML PATH ('') ), 1, 1, '') FROM [Application].[TransactionTypes] GROUP BY TransactionTypeID
Selbiges gilt natürlich auch für STRING_AGG
wo das “Aggregat” ja bereits im Namen steckt. Hier müssen wir stattdessen schreiben:
SELECT TransactionTypeID ,STRING_AGG(TransactionTypeName, ',') FROM [Application].[TransactionTypes] GROUP BY TransactionTypeID
Doch Obacht, nun unterscheidet sich auch die Ausgabe der beiden Abfragen. Während die STUFF
-Abfrage nämlich die Sub-Query für jede Zeile der Ergebnismenge ausführt, also auf jede Zeile die gesamte kommaseparierte Liste schreibt, schreibt die zweite Abfrage nur diejenigen TransactionTypeNames
auf die Zeile, die zur entsprechenden TransactionTypeID
gehören, also jeweils nur einen Eintrag. Ist das nicht das Verhalten, das wir suchen, so müssten wir auch hier mit einer Unterabfrage, einer CTE oder OUTER APPLY
arbeiten:
SELECT TransactionTypeID ,subqery.CSV FROM [Application].[TransactionTypes] OUTER APPLY ( SELECT STRING_AGG(TransactionTypeName, ',') as CSV FROM [Application].[TransactionTypes] ) subqery
Was ihr heute aus dem Dojo mitnehmen solltet: Wenn ihr eine Datenbank verwendet, die dieses Feature unterstützt, verwendet STRING_AGG
, das ist die wesentlich einfachere, elegantere und oftmals sogar performantere Art, Zeichenketten einer Spalte zu verknüpfen. Passt aber auf, wenn ihr in alten Applikationen die STUFF
-Formulierung 1:1 ersetzt, es könnte sein, dass euch die Aggregation einen Strich durch die Rechnung macht, testet also auf jeden Fall eure Resultate… Aber das macht ihr als echte Ninjas ja sowieso.