Concatenate rows into one string, what are the different ways and what are they called?

Below are two queries, I’m trying to workout the following questions while on my sql learning quest.

1. Query 1 – What would this be called in sql? I have tried to Web search for usage examples but do not know what to search on.

2. Query 1 and 2 give me the same result, are they different? Execution Plans look similar except last one has one extra step: UDX Cost 0% step

3. Which would be preferred for putting row into a string?

4. Are these the only ways in sql to put rows into one string

Many thanks.

— Query 1

declare @string varchar(max)    
select @string = coalesce(@string, '') + coalesce(col1, '')     
from   
(
   select '1' as col1    
   Union    
   select '2' as col1    
   Union    
   select '3' as col1    
   Union    
   select '4' as col1          
) x

select @string;

— Query 2

with cte_string
as
(
 select '1' as col1
 union
 select '2' as col1
 Union
 select '3' as col1
 Union
 select '4' as col1
)

select cast(col1 as nvarchar(1))
from cte_string    
for xml path(''), type;

Answer

As you’ve noticed there are multiple ways of concatenating strings in SQL. I’m not certain there are specific names for these used methods. I would describe it as “I’m concatenating strings using …”.

Preferred really depends on your personal taste more than anything else. For example my preferred method would be using STRING_AGG even if it is slower because of the reasons below. So do yourself a favor and update your SQL server.

1. It’s a built-in function. Updates in later versions could make it faster without you having to change any code.
2. The intent is much clearer than other methods. If someone else browses your SQL there is no question about what you’re trying to accomplish.
3. Easier to expand. Unless you know your way around SQL, if you wanted to add a comma or any other separator between every single value, you’d have to think what you’d have to change to accomplish this. With STRING_AGG it’s just a matter of changing a parameter. Using a GROUP BY on a query with STRING_AGG is trivial. On the other methods, not so much.

Concatenating using COALESCE:

DECLARE @string VARCHAR(MAX)

SELECT @string = COALESCE(@string, '') + COALESCE([value], '')
FROM (
    SELECT '1' AS [value]
    UNION
    SELECT '2'
) AS [t]

SELECT @string

Concatenating using FOR XML PATH:

DECLARE @string VARCHAR(MAX)

SELECT @string = (
SELECT '' + [value]
FROM (
    SELECT '1' AS [value]
    UNION
    SELECT '2'
) AS [t]
FOR XML PATH(''))

SELECT @string

Concatenating using common table expression.

WITH cte_string AS
(
    SELECT '1' AS [value]
    UNION
    SELECT '2'
)
SELECT '' + [value]
FROM cte_string
FOR XML PATH('')

Concatenating using the new STRING_AGG function in SQL 2017:

DECLARE @string VARCHAR(MAX)

SELECT @string = STRING_AGG([value], '')
FROM (
    SELECT '1' AS [value]
    UNION
    SELECT '2'
) AS [t]

SELECT @string