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?

2
  • 3
    What code are you using at the moment? What version of SQL Server? Nov 2 '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 5 '19 at 7:14
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;
1
  • 2
    @SadiqabbasHirani And that is why you should always specify the version. Nov 2 '18 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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