What do CONCAT and CONCAT_WS do?
The CONCAT
function is a function from the field of string manipulation. Similar to what you already know from STUFF
and STRING_AGG
, these functions link strings together. Unlike STRING_AGG
and STUFF
in combination with SELECT FOR XML
, however, CONCAT
and CONCAT_WS
do not aggregate rows, i.e. the entries to be combined must already be in columns.
How to use CONCAT and CONCAT_WS
Let’s first look at the difference between STRING_AGG
and CONCAT
, but first remember the query we used to demonstrate STRING_AGG
on the WideWorldImporters database:
SELECT STRING_AGG(TransactionTypeName, ',') FROM [Application].[TransactionTypes]
This query returns a string containing the comma-separated values of the TransactionTypeName column. Now let us naively execute the same query with CONCAT
instead of STRING_AGG
:
SELECT CONCAT(TransactionTypeName, ',') FROM [Application].[TransactionTypes]
Here, we simply get each transaction type in the result set with a trailing comma, so the string in the TransactionTypeName
column was linked to the string containing only a comma. An aggregation like STRING_AGG
, however, was not performed. So if we want to generate a list like STRING_AGG
, we first have to bring the rows to columns of the result set. We could do this quite easily with the PIVOT
command, but here we want to limit the syntax to as simple elements as possible, which we already dealt with in the Dojo, so we take a different approach:
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]
It remains to be said that this is not the most efficient way to achieve our goal, but we have now displayed all rows of the table (luckily there were only 13!) in the columns of the result set.
Now we want to summarize them, for this we first choose the following approach:
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
Now we get a string in which the type names are concatenated directly after each other. Of course this is not what we wanted to have. We could now manually insert a string with a comma after each type name, but that would be quite cumbersome, instead we could also use the function CONCAT_WS
, because the addition _WS
stands for “With Separator”, which is exactly what we want. The separator is passed as the first argument of the function. In the end, it looks like this:
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
As a result, we now have the comma-separated list we got from the beginning with STRING_AGG
again. So what does this mean? If we want to concatenate data, in rows one below the other, we use STRING_AGG
. But if the data is in columns side by side, then CONCAT
or CONCAT_WS
is the method of choice. Happy concatenating.