CONCAT and CONCAT_WS

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.

References

• Documentation from Microsoft on CONCAT
• Documentation from Microsoft on CONCAT_WS