以逗号分隔列表形式返回 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

Answer 2

试试这个：

SELECT H.HotelID, H.HotelName, 
Images = (select COALESCE(HotelImageID + ',', '') 
            FROM HotelImages 
            WHERE HotelID = H.id)
FROM Hotels H

Answer 3

COALESCE 不执行您的建议。它返回列表中的第一个非 NULL 值。它不会跨行聚合值。 （尽管我同意它听起来就像是聚合）。

SQL Server 不直接支持字符串连接聚合（例如，MySQL 有 GROUP_CONCAT）。我已经看到一些提供此类功能的解决方案，但我很确定它们适用于 SQL-2005 及更高版本。 Google（或在此处搜索）“GROUP_CONCAT FOR SQL SERVER”。