How to use COALESCE with multiple rows and without preceding comma?
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;
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];
Thanks Aaron. My current solution is near identical to yours except I'm using DISTINCT instead of GROUP BY.
@user2732180 You should use a GROUP BY as it is more likely to perform the concatenation once per state. With DISTINCT it will apply the same concatenation for every instance of California, for example, and only then throw away all the work it did generating those duplicates.
Just to add to Aaron's answer above...
Be aware that an
ORDER BYmay 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