如何在 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中文网其他相关文章！