如何创建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
这个是可能的吗?或者类似的东西?