Readers' mail: concatenate text from multiple rows ... exceptions and limitations

Hi guys,

Today I wanted to answer mr. Brian Walker who wrote me an email and pointed out a series of important notes about the concatenation of the text we talked about yesterday

I thank him because what I like most is the exchange of ideas!

Brian sent me a script where we see that the syntax we talked about yesterday has some limitations.
Let's see them.

First of all he created a table #name and filled it with some data

       
CREATE TABLE #Names (Name varchar(20))

INSERT #Names
VALUES ('X1')
     , ('X2')
     , ('X3')
     , ('Y1')
     , ('Y2')
     , ('Y3')
     , ('Z1')
     , ('Z2')
     , ('Z3')
   

These are our data...

He showed me that putting in the "order by" clause the field name it's ok!

         
DECLARE @Names varchar(1000)
SELECT @Names = ISNULL(@Names, '') + ' ' + N.Name FROM #Names AS N ORDER BY N.Name
SELECT @Names


SET @Names = NULL
SELECT @Names = ISNULL(@Names, '') + ' ' + N.Name FROM #Names AS N ORDER BY N.Name DESC
SELECT @Names
   

These are the results:

Problems arises when we try to write...

        

SET @Names = NULL

SELECT @Names = ISNULL(@Names, '') + ' ' + N.Name FROM #Names AS N ORDER BY SUBSTRING(N.Name, 2, 1)

SELECT @Names

In fact we obtain an incorrect result:

Yes, the T-SQL syntax can have some problems ..

How to remedy?

Try for example this syntax:

        
SELECT @Names = STRING_AGG(N.Name, ' ') WITHIN GROUP (ORDER BY SUBSTRING(N.Name, 2, 1), SUBSTRING(N.Name, 1, 1)) FROM #Names AS N -- SQL Server 2017 and newer

SELECT @Names

...and here is the correct result:

 

Thank you Brian! ....I owe you a coffe!

 

See you tomorrow for the next post and follow me on linkedin if you like my posts!

Luca

 

Luca Biondi @ SQLServerPerformance blog!

Next post:

Previous post: SQL Server, Concatenates text from multiple rows into a single string!