Was tun CONCAT und CONCAT_WS?
Die CONCAT
-Funktion ist eine Funktion aus dem Bereich der Zeichenketten-Manipulation. Ähnlich wie ihr es schon bei STUFF
und STRING_AGG
kennengelernt hattet, verknüpfen diese Funktionen Zeichenketten miteinander. Anders als STRING_AGG
und STUFF
in Kombination mit SELECT FOR XML
, aggregieren CONCAT
und CONCAT_WS
allerdings keine Zeilen, das heißt die Einträge, die zusammengefasst werden sollen, müssen bereits in Spalten vorliegen.
Wie verwendet man CONCAT und CONCAT_WS?
Sehen wir uns zunächst den Unterschied zwischen STRING_AGG
und CONCAT
an, dafür erinnern wir uns zunächst an die Abfrage, anhand derer wir STRING_AGG
auf der WideWorldImporters Datenbank demonstriert hatten:
SELECT STRING_AGG(TransactionTypeName, ',') FROM [Application].[TransactionTypes]
Diese Abfrage liefert eine Zeichenkette, die die kommaseparierten Werte der Spalte TransactionTypeName enthält. Führen wir nun naiv dieselbe Abfrage mit CONCAT
anstelle von STRING_AGG
aus:
SELECT CONCAT(TransactionTypeName, ',') FROM [Application].[TransactionTypes]
Hier erhalten wir in der Ergebnismenge einfach nur jeden Transaktionstypen mit einem nachgestellten Komma, es wurde also die Zeichenkette der Spalte TransactionTypeName mit der Zeichenkette, die nur ein Komma enthält, verknüpft. Eine Aggregation wie STRING_AGG
wurde hingegen nicht durchgeführt. Wenn wir also eine Liste wie bei STRING_AGG
erzeugen möchten, dann müssen wir zunächst die Zeilen auf Spalten der Ergebnismenge bringen. Das könnten wir nun mit dem PIVOT
-Befehl recht einfach tun, doch möchten wir hier die Syntax auf möglichst einfache Elemente beschränken, die wir bereits im Dojo behandelt hatten, daher wählen wir einen anderen Ansatz:
SELECT TypeName1 = MIN( CASE WHEN TransactionTypeID = 1 THEN TransactionTypeName ELSE NULL END) ,TypeName2 = MIN( CASE WHEN TransactionTypeID = 2 THEN TransactionTypeName ELSE NULL END) ,TypeName3 = MIN( CASE WHEN TransactionTypeID = 3 THEN TransactionTypeName ELSE NULL END) ,TypeName4 = MIN( CASE WHEN TransactionTypeID = 4 THEN TransactionTypeName ELSE NULL END) ,TypeName5 = MIN( CASE WHEN TransactionTypeID = 5 THEN TransactionTypeName ELSE NULL END) ,TypeName6 = MIN( CASE WHEN TransactionTypeID = 6 THEN TransactionTypeName ELSE NULL END) ,TypeName7 = MIN( CASE WHEN TransactionTypeID = 7 THEN TransactionTypeName ELSE NULL END) ,TypeName8 = MIN( CASE WHEN TransactionTypeID = 8 THEN TransactionTypeName ELSE NULL END) ,TypeName9 = MIN( CASE WHEN TransactionTypeID = 9 THEN TransactionTypeName ELSE NULL END) ,TypeName10 = MIN( CASE WHEN TransactionTypeID = 10 THEN TransactionTypeName ELSE NULL END) ,TypeName11 = MIN( CASE WHEN TransactionTypeID = 11 THEN TransactionTypeName ELSE NULL END) ,TypeName12 = MIN( CASE WHEN TransactionTypeID = 12 THEN TransactionTypeName ELSE NULL END) ,TypeName13 = MIN( CASE WHEN TransactionTypeID = 13 THEN TransactionTypeName ELSE NULL END) FROM [Application].[TransactionTypes]
Dass das nicht die effizienteste Art ist, unser Ziel zu erreichen, sei dahingestellt, dennoch haben wir nun alle Zeilen der Tabelle (zum Glück waren es nur 13!) in den Spalten der Ergebnismenge dargestellt.
Nun möchten wir diese zusammenfassen, dafür wählen wir zunächst folgenden Ansatz:
WITH cte_columns AS ( SELECT TypeName1 = MIN( CASE WHEN TransactionTypeID = 1 THEN TransactionTypeName ELSE NULL END) ,TypeName2 = MIN( CASE WHEN TransactionTypeID = 2 THEN TransactionTypeName ELSE NULL END) ,TypeName3 = MIN( CASE WHEN TransactionTypeID = 3 THEN TransactionTypeName ELSE NULL END) ,TypeName4 = MIN( CASE WHEN TransactionTypeID = 4 THEN TransactionTypeName ELSE NULL END) ,TypeName5 = MIN( CASE WHEN TransactionTypeID = 5 THEN TransactionTypeName ELSE NULL END) ,TypeName6 = MIN( CASE WHEN TransactionTypeID = 6 THEN TransactionTypeName ELSE NULL END) ,TypeName7 = MIN( CASE WHEN TransactionTypeID = 7 THEN TransactionTypeName ELSE NULL END) ,TypeName8 = MIN( CASE WHEN TransactionTypeID = 8 THEN TransactionTypeName ELSE NULL END) ,TypeName9 = MIN( CASE WHEN TransactionTypeID = 9 THEN TransactionTypeName ELSE NULL END) ,TypeName10 = MIN( CASE WHEN TransactionTypeID = 10 THEN TransactionTypeName ELSE NULL END) ,TypeName11 = MIN( CASE WHEN TransactionTypeID = 11 THEN TransactionTypeName ELSE NULL END) ,TypeName12 = MIN( CASE WHEN TransactionTypeID = 12 THEN TransactionTypeName ELSE NULL END) ,TypeName13 = MIN( CASE WHEN TransactionTypeID = 13 THEN TransactionTypeName ELSE NULL END) FROM [Application].[TransactionTypes] ) SELECT CONCAT( TypeName1 ,TypeName2 ,TypeName3 ,TypeName4 ,TypeName5 ,TypeName6 ,TypeName7 ,TypeName8 ,TypeName9 ,TypeName10 ,TypeName11 ,TypeName12 ,TypeName13) FROM cte_columns
Nun erhalten wir eine Zeichenkette in der die Typnamen direkt hintereinander konkateniert wurden. Das ist natürlich nicht das, was wir haben wollten. Wir könnten nun manuell hinter jeden Typename einen String mit Komma einfügen, doch das wäre ziemlich mühselig, stattdessen können wir hierfür auch die Funktion CONCAT_WS
verwenden, der Zusatz _WS steht nämlich für “With Separator”, was ja genau das ist, was wir möchten. Der Separator wird dabei als erstes Argument der Funktion übergeben. Am Ende sieht das so aus:
WITH cte_columns AS ( SELECT TypeName1 = MIN( CASE WHEN TransactionTypeID = 1 THEN TransactionTypeName ELSE NULL END) ,TypeName2 = MIN( CASE WHEN TransactionTypeID = 2 THEN TransactionTypeName ELSE NULL END) ,TypeName3 = MIN( CASE WHEN TransactionTypeID = 3 THEN TransactionTypeName ELSE NULL END) ,TypeName4 = MIN( CASE WHEN TransactionTypeID = 4 THEN TransactionTypeName ELSE NULL END) ,TypeName5 = MIN( CASE WHEN TransactionTypeID = 5 THEN TransactionTypeName ELSE NULL END) ,TypeName6 = MIN( CASE WHEN TransactionTypeID = 6 THEN TransactionTypeName ELSE NULL END) ,TypeName7 = MIN( CASE WHEN TransactionTypeID = 7 THEN TransactionTypeName ELSE NULL END) ,TypeName8 = MIN( CASE WHEN TransactionTypeID = 8 THEN TransactionTypeName ELSE NULL END) ,TypeName9 = MIN( CASE WHEN TransactionTypeID = 9 THEN TransactionTypeName ELSE NULL END) ,TypeName10 = MIN( CASE WHEN TransactionTypeID = 10 THEN TransactionTypeName ELSE NULL END) ,TypeName11 = MIN( CASE WHEN TransactionTypeID = 11 THEN TransactionTypeName ELSE NULL END) ,TypeName12 = MIN( CASE WHEN TransactionTypeID = 12 THEN TransactionTypeName ELSE NULL END) ,TypeName13 = MIN( CASE WHEN TransactionTypeID = 13 THEN TransactionTypeName ELSE NULL END) FROM [Application].[TransactionTypes] ) SELECT CONCAT_WS( ',' ,TypeName1 ,TypeName2 ,TypeName3 ,TypeName4 ,TypeName5 ,TypeName6 ,TypeName7 ,TypeName8 ,TypeName9 ,TypeName10 ,TypeName11 ,TypeName12 ,TypeName13) FROM cte_columns
Nun haben wir als Ergebnis wieder die Kommaseparierte Liste, die wir von Anfang an mit STRING_AGG
erhalten hatten. Was bedeutet das also? Wenn wir Daten konkatenieren möchten, in Zeilen untereinander stehen, verwenden wir STRING_AGG
. Wenn die Daten aber in Spalten nebeneinander stehen, dann ist CONCAT
beziehungsweise CONCAT_WS
das Mittel der Wahl. Happy concatenating.