All we need is an easy explanation of the problem, so here it is.
I’m trying to achieve the following:
California | Los Angeles, San Francisco, Sacramento Florida | Jacksonville, Miami
Unfortunately, I’m getting “,Los Angeles, San Francisco, Sacramento, Jacksonville, Miami”
I can achieve my desired results using the STUFF function, but was wondering if there’s a cleaner way of doing it using COALESCE?
STATE | CITY California | San Francisco California | Los Angeles California | Sacramento Florida | Miami Florida | Jacksonville DECLARE @col NVARCHAR(MAX); SELECT @col= COALESCE(@col, '') + ',' + city FROM tbl where city = 'California'; SELECT @col;
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This might be the cleaner approach you’re after. Basically, check if the variable has been initialized yet. If it hasn’t, set it to the empty string, and append the first city (no leading comma). If it has, then append a comma, then append the city.
DECLARE @col nvarchar(MAX); SELECT @col = COALESCE(@col + ',', '') + city FROM dbo.tbl WHERE state = 'California';
Of course, that only works for populating a variable per state. If you are pulling the list for each state one at a time, there is a better solution in one shot:
SELECT [state], cities = STUFF(( SELECT N', ' + city FROM dbo.tbl WHERE [state] = x.[state] FOR XML PATH(''), TYPE).value(N'.', N'nvarchar(max)'), 1, 2, N'') FROM dbo.tbl AS x GROUP BY [state] ORDER BY [state];
state cities ---------- -------------------------------------- California San Francisco, Los Angeles, Sacramento Florida Miami, Jacksonville
To order by city name within each state:
SELECT [state], cities = STUFF(( SELECT N', ' + city FROM dbo.tbl WHERE [state] = x.[state] ORDER BY city FOR XML PATH(''), TYPE).value(N'.', N'nvarchar(max)'), 1, 2, N'') FROM dbo.tbl AS x GROUP BY [state] ORDER BY [state];
In Azure SQL Database or SQL Server 2017+, you can use the new
SELECT [state], cities = STRING_AGG(city, N', ') FROM dbo.tbl GROUP BY [state] ORDER BY [state];
And ordered by city name:
SELECT [state], cities = STRING_AGG(city, N', ') WITHIN GROUP (ORDER BY city) FROM dbo.tbl GROUP BY [state] ORDER BY [state];
Just to add to Aaron’s answer above…
Be aware that an
ORDER BY may break by only including the last item in your query. In my case, I was not grouping, so not sure if that makes a difference. I’m using SQL 2014. In my case, I have something like value1, value2, value3… but my result in the variable was only value3.
Aaron commented to say:
This has been reported at least four times on Connect:
- In Variable concatenation and order by filters results (like where condition)
- (n)varchar building from ResultSet fails when ORDER BY is added
- Assigning a local variable from an ordered SELECT with CROSS APPLYs and a table-valued function only returns last value
- When concatenating varchar(max)/nvarchar(max) values from a table variable, incorrect results may be returned if filtering and ordering by a non-primary-key column
Example response from Microsoft:
The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior.
The response also references KB 287515:
The solution is to use
FOR XML PATH (the second approach in Aaron’s answer) if the order of concatenation is important and, of course, if you want to be sure to include all values. Also see:
nvarchar concatenation / index / nvarchar(max) inexplicable behavior on Stack Overflow
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂