SQL Grouping and Nested Concatenation

Question

Sample data :

ID	Continent	Country	City
1	Africa	Egypt	Cairo
2	Africa	Egypt	Alexandria
3	Africa	Egypt	Luxor
4	Africa	Morocco	Rabat
5	Africa	Morocco	Casablanca
6	Asia	China	Beijing
7	Asia	China	Shanghai

The output I need is :

ID	Continent	CountryAndCities
1	Africa	Egypt(Cairo,Alexandria,Luxor) - Morocco(Rabat,Casablanca)
2	Asia	China(Beijing,Shanghai)

I tried this code:

SELECT DISTINCT
    Country,
    ISNULL(STUFF((SELECT CONCAT('- ', City)
                  FROM Continental s2
                  WHERE s2.Country = s.Country
                  GROUP BY City
                  FOR XML PATH ('')), 1, 1, ''), '') AS Cities
FROM
    Continental s
GROUP BY
    Country

I get the following output:

Country	Cities
Egypt	Cairo- Alexandria- Luxor
Morocco	Casablanca- Rabat
China	Beijing- Shanghai

How can I get my desired output (shown above)?

Answer 1

Try this:

with cities as (
    select Continent, 
       Country + '(' + string_agg(City, ',') + ')' as Cities
    from continental
    group by Continent, Country
)
select 
    row_number() over (order by Continent) ID,
    Continent,
    string_agg(cities, ' - ') CountryAndCities
from cities
group by Continent
order by Continent

See it work here:

https://dbfiddle.uk/G2XJX6jE

This uses string_agg(), which does require at least SQL Server 2017. So it's possible if you're running SQL Server 2016 (or SQL Server 2014, which is supported for just less than another two weeks 😕) you may need to do something else. If you have anything older, it's time to upgrade.