sql - 加入逗号分隔的值

我有2张桌子，如下所示:

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

我想写一个查询，其结果如下:
TRAIN表中的CITY_TRAVELS也是VARCHAR类型
CITY表的CITY_ID为INT类型。
查询结构将如何？

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

2222     Delhi

3333     London,Chicago

4444     Chicago,Newyork

谢谢
萨蒂亚吉特

最佳答案

-- 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

编辑:删除了第二个解决方案，因为经过一些测试后，它显示出不合适的性能，尽管它对表的引用较少。

关于sql - 加入逗号分隔的值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7761773/