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

Question

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 1

Check out the COALESCE function. This article explains how to use it to get rows into a comma-delimited string: http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Answer 2

I don't belive there's a way to do it within one query, but you can play tricks like this with a temporary variable:

declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations

select @s

It's definitely less code than walking over a cursor, and probably more efficient.

Answer 3

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [VehicleID]
     , [Name]
     , (SELECT CAST(City + ', ' AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')
      ) AS Locations
FROM [Vehicle]

It's a lot easier than using a cursor, and seems to work fairly well.

Answer 4

Note that Matt's code above will result in an extra comma at the end of the string; using COALESCE (or ISNULL for that matter) as shown in the link in Lance's post uses a similar method but doesn't leave you with an extra comma to remove. For the sake of completeness, here's the relevant code from Lance's link on sqlteam.com:

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
    CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

Answer 5

If you're running Sql Server 2005, you can write a custom aggregate function to handle this.

C# version:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSV:IBinarySerialize
{
    private StringBuilder Result;
    public void Init() {
     this.Result = new StringBuilder();
    }

    public void Accumulate(SqlString Value) {
     if (Value.IsNull) return;
     this.Result.Append(Value.Value).Append(",");
    }
    public void Merge(CSV Group) {
     this.Result.Append(Group.Result);
    }
    public SqlString Terminate() {
     return new SqlString(this.Result.ToString());
    }
    public void Read(System.IO.BinaryReader r) {
     this.Result = new StringBuilder(r.ReadString());
    }
    public void Write(System.IO.BinaryWriter w) {
     w.Write(this.Result.ToString());
    }
}

Answer 6

i've wanted a CONCAT aggregate operator.

Like there is

SELECT SUM(column)
SELECT AVG(column)
SELECT STDEV(column)

i want a

SELECT CONCAT(column, ', ')

Answer 7

The below code will work for Sql Server 2000/2005/2008

CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)
RETURNS VARCHAR(1000) AS
BEGIN
  DECLARE @csvCities VARCHAR(1000)
  SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')
  FROM Vehicles 
  WHERE VehicleId = @VehicleId 
  return @csvCities
END

-- //Once the User defined function is created then run the below sql

SELECT VehicleID
     , dbo.fnConcatVehicleCities(VehicleId) AS Locations
FROM Vehicles
GROUP BY VehicleID

Answer 8

VERSION NOTE: You must be using SQL Server 2005 or greater with Compatibility Level set to 90 or greater for this solution.

See this MSDN article for the first example of creating a user-defined aggregate function that concatenates a set of string values taken from a column in a table.

My humble recommendation would be to leave out the appended comma so you can use your own ad-hoc delimiter, if any.

Referring to the C# version of Example 1:

change:  this.intermediateResult.Append(value.Value).Append(',');
    to:  this.intermediateResult.Append(value.Value);

And

change:  output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
    to:  output = this.intermediateResult.ToString();

That way when you use your custom aggregate, you can opt to use your own delimiter, or none at all, such as:

SELECT dbo.CONCATENATE(column1 + '|') from table1

NOTE: Be careful about the amount of the data you attempt to process in your aggregate. If you try to concatenate thousands of rows or many very large datatypes you may get a .NET Framework error stating "[t]he buffer is insufficient."

Answer 9

From what I can see FOR XML (as posted earlier) is the only way to do it if you want to also select other columns (which I'd guess most would) as the OP does. Using COALESCE(@var... does not allow inclusion of other columns.

Update: Thanks to programmingsolutions.net there is a way to remove the "trailing" comma to. By making it into a leading comma and using the STUFF function of MSSQL you can replace the first character (leading comma) with an empty string as below:

stuff((select ',' + Column from Table inner where inner.Id = outer.Id for xml path('')), 1,1,'') as Values

Answer 10

IN sql 2005,

SELECT [VehicleID]
    , [Name]
    , [Locations] = Isnull( Stuff(
        ( SELECT N', ' + [City] FROM [Locations]
        WHERE VehicleID = a.VehicleID
        FOR XML PATH(''),TYPE ).value('text()[1]', 'nvarchar(max)')
    , 1, 2, N''), N'')
FROM [Vehicle] a