SQL Server – How to Use COALESCE with Multiple Rows Without Preceding Comma

sql servert-sql

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

Best Answer

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