As one example of, think that I've two tables the following:
VehicleID Title 1 Chuck 2 Ray LocationID VehicleID City 1 1 New You are able to 2 1 Dallas 3 1 Vancouver 4 2 La 5 2 Houston
I wish to write a question to come back the next results:
VehicleID Title Locations 1 Chuck New You are able to, Dallas, Vancouver 2 Ray La, Houston
I understand that you can do this 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 Title varchar(100) Locations varchar(4000) ) DECLARE VehiclesCursor CURSOR FOR Choose [VehicleID] , [Title] FROM [Automobiles] OPEN VehiclesCursor FETCH NEXT FROM VehiclesCursor INTO @VehicleID , @VehicleName WHILE @@FETCH_STATUS = BEGIN SET @Locations = '' DECLARE LocationsCursor CURSOR FOR Choose [City] FROM [Locations] WHERE [VehicleID] = @VehicleID OPEN LocationsCursor FETCH NEXT FROM LocationsCursor INTO @LocationCity WHILE @@FETCH_STATUS = BEGIN SET @Locations = @Locations + @LocationCity FETCH NEXT FROM LocationsCursor INTO @LocationCity Finish CLOSE LocationsCursor DEALLOCATE LocationsCursor Place INTO @Results (VehicleID, Title, Locations) Choose @VehicleID, @Title, @Locations Finish CLOSE VehiclesCursor DEALLOCATE VehiclesCursor Choose * FROM @Results
However, as you can tell, this involves a lot of code. What I'd like is really a generic function that will let me make a move such as this:
Choose VehicleID , Title , JOIN(Choose City FROM Locations WHERE VehicleID = Automobiles.VehicleID, ', ') AS Locations FROM Automobiles
Is possible? Or something like that similar?
If you are using SQL Server 2005, you could utilize the FOR XML PATH command.
Choose [VehicleID] , [Title] , (Choose CAST(City + ', ' AS VARCHAR(MAX)) FROM [Location] WHERE (VehicleID = Vehicle.VehicleID) FOR XML PATH ('') ) AS Locations FROM [Vehicle]
It is a lot simpler than utilizing a cursor, and appears to operate fairly well.
Observe that Matt's code above can lead to an additional comma in the finish from the string using COALESCE (or ISNULL for your matter) as proven within the link in Lance's publish utilizes a similar method but does not give you an additional comma to get rid of. With regard to completeness, here's the appropriate code from Lance's link on sqlteam.com:
DECLARE @EmployeeList varchar(100) Choose @EmployeeList = COALESCE(@EmployeeList + ', ', '') + CAST(EmpUniqueID AS varchar(5)) FROM SalesCallsEmployees WHERE SalCal_UniqueID = 1
I do not belive there's a method to get it done within one query, however, you can enjoy methods such as this having a temporary variable:
declare @s varchar(max) set @s = '' choose @s = @s + City + ',' from Locations choose @s
It's certainly less code than walking on the cursor, and most likely more effective.
Browse the COALESCE function. This short article describes using it to obtain rows right into a comma-delimited string: http://world wide web.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
The below code is useful for Sql Server 2000/2005/2008
CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT) RETURNS VARCHAR(1000) AS BEGIN DECLARE @csvCities VARCHAR(1000) Choose @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'') FROM Automobiles WHERE VehicleId = @VehicleId return @csvCities Finish -- //When the User defined function is produced then run the below sql Choose VehicleID , dbo.fnConcatVehicleCities(VehicleId) AS Locations FROM Automobiles GROUP BY VehicleID