I has two tables on MS SQL Server. First with the cities, second with student names and city ID from first table, where this student lives.

I need a sql query that will return results like this:

Stella Paris

Bob Moscow,New York

Mary Paris,New York

CodePudding user response：

WITH CITIES(ID,CITY_NAME)AS
(
    SELECT 1,'MOSCOW' UNION ALL
    SELECT 2,'PARIS' UNION ALL
    SELECT 3,'NEW YORK'
 ),
 STUDENTS(ID,STUDENT_NAME,LIVE_IN) AS
 (
   SELECT 1,'STELLA','2' UNION ALL
   SELECT 2,'BOB','1,3' UNION ALL
   SELECT 3,'MARY','2,3'
 )
 SELECT X.ID,X.STUDENT_NAME,STRING_AGG(X.CITY_NAME,',')CITY_NAME FROM
  ( 
     SELECT S.ID,S.STUDENT_NAME,VALUE AS CITY_ID,C.CITY_NAME
     FROM STUDENTS AS S
     CROSS APPLY string_split(S.LIVE_IN,',')
     JOIN CITIES AS C ON VALUE=C.ID
  )X
 GROUP BY X.ID,X.STUDENT_NAME;

For SQL Server 2017 and later, as pointed by @Squirrel, you can use STRING_SPLIT and STRING_AGG

CodePudding user response：

Steps you can use:-

1. Use two different queries
2. first returning students records
3. then loop through to get cities records
4. update the array data as required

A better suggestion would be redesign database so as to be used table JOIN