JOINING comma separated values

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    开发者_C百科 Chicago,Newyork

Thanks Satyajit

---

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!

---

-- sample data
declare @City table
(
    CityID int,
    City varchar(50)
)

declare @Train table
(
    TrainID int,
    CityTravels varchar(50)
)

insert into @City 
select 1, 'London'
union all
select 2, 'Chicago'
union all
select 3, 'Newyork'
union all
select 4, 'Delhi'

insert into @Train
select 1111, '1,3'
union all
select 2222, '4'
union all
select 3333, '1,2'
union all
select 4444, '2,3'


-- solution
;with cte as
(
    select
        t1.TrainID, t2.City
    from
    (
        select
            TrainID, 
            -- t2.c - the xml tag that function nodes provides
            -- query('data(.)') gets the value of the tag
            CityID = cast(cast(t2.c.query('data(.)') as varchar) as int)
        from
        (
            select 
                TrainID,
                -- represent Cities list in the xml, 
                -- which is proper for xquery node function
                -- for example <root><a>1</a><a>2</a></root>
                CityTravelsXml = cast('<root><a>' + replace(CityTravels, ',', '</a><a>') + '</a></root>' as xml)
            from @Train
        ) t1
        -- xquery nodes function for each tag /root/a returns a separate row 
        -- with single column the represents the tag
        cross apply CityTravelsXml.nodes('/root/a') t2(c)
    ) t1
    join @City t2 on t1.CityID = t2.CityID
)


select
    TrainID,
    -- truncate the last comma
    Cities = case when Cities is not null then substring(Cities, 1, len(Cities) - 1) end
from
(
    select 
        TrainID, 
        Cities = 
        (
            -- for xml here concatenates strings in a column
            select 
                City + ',' 
            from cte t2 
            where t2.TrainID = t1.TrainID 
            for xml path('')
        ) 
    from @Train t1
) t

Edit: Removed the second solution, because after some testing it showed inappropriate performance, inspite it has less references to tables.

---

create database train

create table city(
city_id int identity(1,1),
city varchar(max)
)

create table train(
train_id int identity(1111,1111),
city_travels varchar(max)
)

insert into city values ('London');
insert into city values ('Chicago');
insert into city values ('NewYork');
insert into city values ('Delhi');

insert into train values ('1,3,4');
insert into train values ('4');
insert into train values ('1,2');
insert into train values ('1,2,3,4');

create table #train(
train_id int,
city varchar(max)
)
declare @count int, @id int,@first int;
declare @train_id int,@index int;
declare @city_travels varchar(max),@city_name varchar(max);
set @city_name=null;

declare train_cursor CURSOR for
select train_id,city_travels from train
open train_cursor
fetch next from train_cursor into @train_id,@city_travels

    while (@@fetch_status=0)
    begin
        set @first=0;
        set @index = charindex(',',@city_travels);
            if(@index!=0)
            begin
                if(@first=0)
                begin
                    set @id=convert (int,substring(@city_travels,1,@index-1));
                    set @city_travels=substring(@city_travels,@index+1,len(@city_travels));
                    set @city_name=((select city from city where city_id=@id)+',');
                    set @index=charindex(',',@city_travels);
                    set @first=1;
                end
                while(@index!=0)
                begin
                    set @id=convert (int,substring(@city_travels,1,@index-1));
                    set @city_travels=substring(@city_travels,@index+1,len(@city_travels));
                    set @index=charindex(',',@city_travels)
                    set @city_name=(@city_name+(select city from city where city_id=@id)+',');
                end
            set @id=convert (int,@city_travels);
            set @city_name=(@city_name+(select city from city where city_id=@id));
            insert into #train values (@train_id,@city_name);
            set @city_name=null;
            end
        else
        begin
            set @id=convert (int,@city_travels);
            set @city_name=(select city from city where city_id=@id);
            insert into #train values (@train_id,@city_name);
            set @city_name=null;
        end
    fetch next from train_cursor into @train_id,@city_travels
    end
select * from city;
select * from train;
select * from #train;
close train_cursor
deallocate train_cursor
truncate table #train