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!
Luca Biondi @ SQLServerPerformance blog!
Next post:
Previous post: SQL Server, Concatenates text from multiple rows into a single string!