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