How to use COALESCE with DISTINCT with multiple rows and without preceding comma 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?
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;