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.


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?

  • 3
    What code are you using at the moment? What version of SQL Server? Nov 2, 2018 at 10:40
  • It would help if you used actual area names and city names and state names. Your data set as given makes no sense... Is Area 1 in state A or B? Is city A in state A or B? Is area 4 in city B or C?
    – user275801
    Sep 5, 2019 at 7:14

1 Answer 1


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

    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,''),
  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.

  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;
  • 2
    @SadiqabbasHirani And that is why you should always specify the version. Nov 2, 2018 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.