How to use COALESCE with multiple rows and without preceding comma?

Question

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;

Thanks

Answer 1

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'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

Results:

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'.[1]', 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 STRING_AGG() function:

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];

Answer 2

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:

1. In Variable concatenation and order by filters results (like where condition)
2. (n)varchar building from ResultSet fails when ORDER BY is added
3. Assigning a local variable from an ordered SELECT with CROSS APPLYs and a table-valued function only returns last value
4. 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:

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

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