如何创建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

它肯定比游标上的代码少，而且可能更有效率。