如何創(chuàng)建SQLServer函數(shù)將子查詢中的多行“連接”到單個分隔字段中？為了舉例說明，假設我有兩個表，如下所示：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但是，正如您所看到的，這需要大量的代碼。我想要的是一個通用函數(shù)，它允許我這樣做：SELECT VehicleID , Name , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS LocationsFROM Vehicles這個是可能的嗎？或者類似的東西？
如何創(chuàng)建SQLServer函數(shù)將子查詢中的多行“連接”到單個分隔字段中？
倚天杖
2019-06-14 16:27:46
