-1

I have two tables, one for country an the other for the city, and I want a query to get the country with all the cities in a comma-separated which grouped by the countries.

Note:

  1. The second table has a foreign key Fk_CountryId from the primary key of the first table CityId which is a primary key

  2. I am using SQL Server

The first table Country looks like this

CountryId | CountryName 
----------+-------------
    1     | USA 
    2     | UK
    3     | Germany

The second table City looks like this

CityId | CityName   | Fk_CountryId
-------+------------+-------
1      | Los Angeles|   1
2      | Boston     |   1
3      | Cambridge  |   2
4      | Chester    |   2
5      | Berlin     |   3
6      | Hamburg    |   3

The expected DataSet:

CountryId | CountryName  | CitiesNames
----------+--------------+----------------------
1         | USA          | Los Angeles, Boston
2         | UK           | Cambridge, Chester
3         | Germany      | Berlin, Hamburg
IShubh
  • 354
  • 1
  • 3
  • 12
Ahmed Ramadan
  • 25
  • 7
  • Foreign keys are there to ensure data consistency. – jarlh Aug 03 '18 at 13:37
  • https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 – jarlh Aug 03 '18 at 13:38
  • `SELECT C.CountryId,C.CountryName, STRING_AGG(Ci,CityName, ', ') AS CitiesNames FROM Country C INNER JOIN City Ci ON C.CountryId = Ci.Fk_CountryId GROUP BY C.CountryId,C.CountryName;` – Prahalad Gaggar Aug 03 '18 at 13:45
  • Duplicate of: https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv – Prahalad Gaggar Aug 03 '18 at 13:51

1 Answers1

0

For older version of SQL Server i would use FOR XML clause with STUFF() :

SELECT c.CountryId, c.CountryName,
       STUFF( (SELECT DISTINCT ' ,'+ct.CityName   
               FROM City ct
               WHERE ct.CountryId = c.CountryId 
               FOR XML PATH ('')
              ), 1, 1, ''
            ) AS CitiesNames
FROM Country c;

You can use STRING_AGG() for newer version (SQL Server 2017). However, DISTINCT is redundant inside STUFF() if you don't have a duplicate cities. So, you can remove it.

For better performance you need index Country (CountryId) include CountryName, CityName.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52