如何创建 SQL Server 函数以将子查询中的多行“加入”到单个分隔字段中？

小编典典

如何创建 SQL Server 函数以将子查询中的多行“加入”到单个分隔字段中？

all

为了说明，假设我有两个表，如下所示：

VehicleID Name
1         Chuck
2         Larry

LocationID VehicleID City
1          1         New York
2          1         Seattle
3          1         Vancouver
4          2         Los Angeles
5          2         Houston

我想编写一个查询以返回以下结果：

VehicleID Name    Locations
1         Chuck   New York, Seattle, Vancouver
2         Larry   Los Angeles, Houston

我知道这可以使用服务器端游标来完成，即：

DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
  VehicleID int
  Name varchar(100)
  Locations varchar(4000)
)

DECLARE VehiclesCursor CURSOR FOR
SELECT
  [VehicleID]
, [Name]
FROM [Vehicles]

OPEN VehiclesCursor

FETCH NEXT FROM VehiclesCursor INTO
  @VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @Locations = ''

  DECLARE LocationsCursor CURSOR FOR
  SELECT
    [City]
  FROM [Locations]
  WHERE [VehicleID] = @VehicleID

  OPEN LocationsCursor

  FETCH NEXT FROM LocationsCursor INTO
    @LocationCity
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @Locations = @Locations + @LocationCity

    FETCH NEXT FROM LocationsCursor INTO
      @LocationCity
  END
  CLOSE LocationsCursor
  DEALLOCATE LocationsCursor

  INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations

END     
CLOSE VehiclesCursor
DEALLOCATE VehiclesCursor

SELECT * FROM @Results

但是，如您所见，这需要大量代码。我想要的是一个通用函数，它可以让我做这样的事情：

SELECT VehicleID
     , Name
     , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles

这可能吗？或者类似的东西？

阅读 65

2022-06-29

共1个答案

小编典典

如果您使用的是 SQL Server 2005，则可以使用 FOR XML PATH 命令。

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

它比使用游标容易得多，而且似乎工作得很好。

更新

对于仍在使用此方法和较新版本的 SQL Server 的任何人，还有另一种
STRING_AGG方法，使用自 SQL Server 2017 以来可用的方法更容易且性能更高。

SELECT  [VehicleID]
       ,[Name]
       ,(SELECT STRING_AGG([City], ', ')
         FROM [Location]
         WHERE VehicleID = V.VehicleID) AS Locations
FROM   [Vehicle] V

这也允许将不同的分隔符指定为第二个参数，从而比前一种方法提供更多的灵活性。

2022-06-29