Friday 30 December 2016

How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?





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?


Answer



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.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...