Sql-server – How to use COALESCE with DISTINCT with multiple rows and without preceding comma

sql serversql-server-2012

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

I have a similar scenario but with a small difference. I would be grateful if any expert could help me with it.

Data

area | city | state
1 | a | a
2 | a | a
3 | b | a
4 | b | a
1 | a | b
2 | a | b
3 | c | b
4 | c | b

Desired Results

Now I would like to retrieve the results as:

area | city | state
1,2,3,4 | a,b | a
1,2,3,4 | a,c | b

Retrieved Results

I tried myself with the above mentioned solution, but I am receiving the following result set:

area | city | state
1,2,3,4 | a,a,b,b | a
1,2,3,4 | a,a,c,c | b

I need city to be distinct.
I hope I have explained good enough?

Best Answer

Not sure what code you're using now or what version of SQL Server you're using, but FOR XML PATH goes pretty far back. Here's how I would do it:

DECLARE @t TABLE(area int, city char(1), state char(1));

INSERT @t(area,city,state) VALUES
(1,'a','a'),(2,'a','a'),(3,'b','a'),(4,'b','a'),
(1,'a','b'),(2,'a','b'),(3,'c','b'),(4,'c','b');

;WITH x AS
( 
  SELECT 
    area = STUFF((SELECT ',' + CONVERT(varchar(11),area) 
      FROM @t WHERE state = t.state GROUP BY area 
      FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),1,1,''),
    city = STUFF((SELECT ',' + city 
      FROM @t WHERE state = t.state GROUP BY city 
      FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),1,1,''),
    state
  FROM @t AS t
)
SELECT area,city,state 
  FROM x 
  GROUP BY area,city,state 
  ORDER BY area,city,state;

There is certainly a way to do this with STRING_AGG() in more modern versions (2016 SP1+), but it won't be much less messy.

;WITH x AS
(
  SELECT city,state FROM @t GROUP BY state,city
),
y AS
(
  SELECT x.state, city = STRING_AGG(x.city,',') 
  FROM x GROUP BY state
)
SELECT area = STRING_AGG(t.area, ','), y.city, y.state 
  FROM y INNER JOIN @t AS t ON t.state = y.state
  GROUP BY y.city, y.state
  ORDER BY area, y.city, y.state;

Related Solutions

Sql-server – How to use COALESCE with multiple rows and without preceding comma

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

Sql-server – Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.