如何在 SQL Server 中將多個子查詢行組合成單一分隔欄位？

SQL Server：將多個子查詢行組合成單一分隔欄位

本文探討了在 SQL Server 中將子查詢中的行連接到單一分隔欄位中的幾種方法。 我們將研究適合不同 SQL Server 版本的技術。

方法 1：FOR XML PATH（SQL Server 2005 及更高版本）

這個經典方法使用 FOR XML PATH 來有效地連接結果：

SELECT 
    [VehicleID],
    [Name],
    STUFF((SELECT ', ' + [City]
           FROM [Location]
           WHERE (VehicleID = Vehicle.VehicleID)
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Locations
FROM [Vehicle]

方法 2：STRING_AGG（SQL Server 2017 及更高版本）

對於較新的 SQL Server 版本，STRING_AGG 提供了更具可讀性且通常更快的解決方案：

SELECT  
    [VehicleID],
    [Name],
    STRING_AGG([City], ', ') AS Locations
FROM   
    [Vehicle] V
JOIN
    [Location] L ON V.VehicleID = L.VehicleID
GROUP BY
    [VehicleID], [Name]

請注意，在此特定情況下，與子查詢相比，使用 JOIN 可以提高效能。

方法3：自訂函數（所有SQL Server版本）

為了在不同 SQL Server 版本和場景之間實現最大的可重複使用性，自訂函數提供了靈活性：

CREATE FUNCTION dbo.JoinRows (@Subquery NVARCHAR(MAX), @Delimiter NVARCHAR(1))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) = '';
    DECLARE @Row NVARCHAR(MAX);
    DECLARE @NextRow CURSOR;

    SET @NextRow = CURSOR FOR SELECT [Value] FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', @Subquery);

    OPEN @NextRow;
    FETCH NEXT FROM @NextRow INTO @Row;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Result += @Row + @Delimiter;
        FETCH NEXT FROM @NextRow INTO @Row;
    END;

    CLOSE @NextRow;
    DEALLOCATE @NextRow;

    RETURN STUFF(@Result, LEN(@Result), LEN(@Delimiter), ''); -- Remove trailing delimiter
END;
GO

此函數使用OPENROWSET來執行動態子查詢。 這比直接執行動態SQL更安全。 然後可以像這樣使用該函數：

SELECT 
    VehicleID, 
    Name, 
    dbo.JoinRows((SELECT City FROM Location WHERE VehicleID = Vehicle.VehicleID), ',') AS Locations
FROM Vehicle;

選擇最適合您的 SQL Server 版本和效能要求的方法。 對於 SQL Server 2017 及更高版本，由於其簡單性和效能，通常建議使用 STRING_AGG。 對於舊版本，或更複雜的場景，自訂函數提供了強大且可重複使用的解決方案。

以上是如何在 SQL Server 中將多個子查詢行組合成單一分隔欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！