如何创建SQLServer函数将子查询中的多行“连接”到单个分隔字段中？

Question

如何创建SQLServer函数将子查询中的多行“连接”到单个分隔字段中？

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

VehicleID Name1         Chuck2         Larry

LocationID VehicleID City1          1         New York2          1         Seattle3          1        
 Vancouver4          2         Los Angeles5          2         Houston

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

VehicleID Name    Locations1         Chuck   New York, Seattle, Vancouver2         Larry   Los Angeles, Houston

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

DECLARE @VehicleID intDECLARE @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 FORSELECT
  [VehicleID], [Name]FROM [Vehicles]OPEN VehiclesCursorFETCH NEXT FROM VehiclesCursor INTO
  @VehicleID, @VehicleNameWHILE @@FETCH_STATUS = 0BEGIN

  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, 
  @LocationsEND     CLOSE VehiclesCursorDEALLOCATE VehiclesCursorSELECT * FROM @Results

但是，正如您所看到的，这需要大量的代码。我想要的是一个通用函数，它允许我这样做：

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

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

Answer 1

如果使用的是SQLServer 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 LocationsFROM [Vehicle]它比使用游标容易得多，而且似乎运行得相当好。

Answer 2

我不相信在一个查询中存在这样的方法，但是您可以使用一个临时变量来玩这样的把戏：declare @s varchar(max)set @s = ''select @s = @s + City + ',' from Locationsselect @s它肯定比游标上的代码少，而且可能更有效率。