2

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?

John K. N.
  • 17,649
  • 12
  • 51
  • 110
Sadiqabbas Hirani
  • 131
  • 1
  • 4
  • 3
    What code are you using at the moment? What version of SQL Server? – George.Palacios Nov 02 '18 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 05 '19 at 07:14

1 Answers1

3

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;
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614