以逗号分隔的列表形式返回的 SQL 数据

Question

这是我的表:

-----------------------------------------
Hotels
-----------------------------------------
HotelID | HotelName
-----------------------------------------


-----------------------------------------
HotelImages
-----------------------------------------
HotelImageID | HotelID | Filename
-----------------------------------------

我想创建一个 SELECT 语句，它将返回如下数据:

HotelID | HotelName | Images
-----------------------------------------
1 | Hotel1 | Image1,Image2,Image3
2 | Hotel2 | Image4,Image5,Image6

我如何修改我的查询来做到这一点？我有:

SELECT H.HotelID, H.HotelName, '' AS Images
FROM Hotels H

我知道我可以使用 COALESCE 创建一个逗号分隔的列表:

DECLARE @Images varchar(500)
SELECT @Images = COALESCE(@Images + ',', '') + CAST(Filename AS varchar(100)) FROM HotelImages WHERE HotelID = 1
SELECT @Images

但我不知道如何将它集成到我当前的查询中，以便可以与其余的酒店数据一起返回列表。

我应该提到我使用的是 SQL Server 2000。

Answer 1

由于这是 SQL 2000，因此您的选择有些有限。您不能使用 FOR XML PATH 或递归 CTE

此外，COALESCE 技巧仅适用于填充单个酒店的逗号分隔列表。不是所有的人。

老实说，您最好在客户端执行此操作。但是，如果无法做到这一点(某些报告应用程序无法命名)，您可以执行以下操作。

请注意，循环过程的次数等于与酒店关联的最大文件数。这比为每个酒店设置一个循环要好得多(例如，从 select 子句中调用 UDF)。

DECLARE @foo table ( HotelID int , lastID int , FileNameList varchar(8000))

INSERT INTO @Foo 

SELECT
    start.HotelID,
    start.firstID ,
    id.FileName 

FROM
    (SELECT 
        HotelID,
        min(HotelImageID ) firstID
    from 
        HotelImages


    group by HotelID) start
    INNER JOIN HotelImages hi
    ON start.firstID = hi.HotelImageID 


WHILE @@RowCount <> 0 
BEGIN

    UPDATE @foo
    SET 
        FileNameList = FileNameList + ',' + FileName
        lastID  = Xnext.nextID
    FROM 
        @foo f
        INNER JOIN HotelImages hi
        ON f.HotelID = id.HotelID
        INNER JOIN 
        (SELECT 

            id.HotelID,
            min(HotelImageID ) nextID
        from HotelImages hi
              inner join @foo f
              on f.HotelID = id.HotelID
        where id.HotelImageID  > f.lastid


        group by id.HotelID) Xnext

        ON Xnext.nextID = id.HotelImageID 



END 

SELECT 
    h.HotelID 
    h.HotelName,
    f.FileNameList
FROM
    Hotels h
    INNER JOIN @foo f
    ON h.hotelId = f.HotelId