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.
