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

2022-01-30 00:00:00 sql sql-server string-concatenation

为了说明，假设我有两个表如下:

To illustrate, assume that I have two tables as follows:

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

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

I want to write a query to return the following results:

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

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

I know that this can be done using server side cursors, ie:

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

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

However, as you can see, this requires a great deal of code. What I would like is a generic function that would allow me to do something like this:

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

这可能吗?还是类似的?

Is this possible? Or something similar?

推荐答案

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

If you're using SQL Server 2005, you could use the FOR XML PATH command.

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]

这比使用游标容易得多，而且似乎工作得相当好.

It's a lot easier than using a cursor, and seems to work fairly well.

更新

对于仍在使用此方法和较新版本的 SQL Server 的任何人，还有另一种方法，使用STRING_AGG 方法自 SQL Server 2017 起可用.

For anyone still using this method with newer versions of SQL Server, there is another way of doing it which is a bit easier and more performant using the STRING_AGG method that has been available since SQL Server 2017.

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

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

This also allows a different separator to be specified as the second parameter, providing a little more flexibility over the former method.

相关文章