JOINING comma separated values

Question

I have 2 tables as below:

TABLE:CITY

CITY_ID CITY
----------------
1   London

2   Chicago

3   Newyork

4   Delhi


TABLE:TRAIN

TRAIN_ID CITY_TRAVELS
----------------------
1111           1,3

2222           4

3333           1,2

4444           2,3

I want to write a query which should give the below result: Also CITY_TRAVELS in TRAIN table is type VARCHAR and CITY_ID of CITY table is of type INT. How will be the query structure be?

TRAIN_ID  CITY
-------------------------
1111     London,Newyork

2222     Delhi

3333     London,Chicago

4444     Chicago,Newyork

Thanks Satyajit

Answer 1

well the structure is awful but you can parse comma separated string in some UDF and use subquery to join numbers to cities. Hope the article "Split Function in Sql Server to break Comma-Separated Strings into Table" will help you!