<p>To illustrate, assume that I have two tables as follows:</p>

<pre><code>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
</code></pre>

<p>I want to write a query to return the following results:</p>

<pre><code>VehicleID Name    Locations
1         Chuck   New York, Seattle, Vancouver
2         Larry   Los Angeles, Houston
</code></pre>

<p>I know that this can be done using server side cursors, ie:</p>

<pre><code>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
</code></pre>

<p>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:</p>

<pre><code>SELECT VehicleID
     , Name
     , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles
</code></pre>

<p>Is this possible?  Or something similar?</p>

[{'answer_id': 6961, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 6, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>declare @s varchar(max)\nset @s = ''\nselect @s = @s + City + ',' from Locations\n\nselect @s\n</code></pre>\n\n<p>It's definitely less code than walking over a cursor, and probably more efficient.</p>\n"}, {'answer_id': 6980, 'author': 'Mun', 'author_id': 775, 'author_profile': 'https://Stackoverflow.com/users/775', 'pm_score': 9, 'selected': True, 'text': '<p>If you\'re using SQL Server 2005, you could use the FOR XML PATH command.</p>\n<pre><code>SELECT [VehicleID]\n     , [Name]\n     , (STUFF((SELECT CAST(\', \' + [City] AS VARCHAR(MAX)) \n         FROM [Location] \n         WHERE (VehicleID = Vehicle.VehicleID) \n         FOR XML PATH (\'\')), 1, 2, \'\')) AS Locations\nFROM [Vehicle]\n</code></pre>\n<p>It\'s a lot easier than using a cursor, and seems to work fairly well.</p>\n<p><strong>Update</strong></p>\n<p>For anyone still using this method with newer versions of SQL Server, there is another way of doing it which is a bit easier and more performant using the\n<a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15" rel="noreferrer"><code>STRING_AGG</code></a> method that has been available since SQL Server 2017.</p>\n<pre><code>SELECT  [VehicleID]\n       ,[Name]\n       ,(SELECT STRING_AGG([City], \', \')\n         FROM [Location]\n         WHERE VehicleID = V.VehicleID) AS Locations\nFROM   [Vehicle] V\n</code></pre>\n<p>This also allows a different separator to be specified as the second parameter, providing a little more flexibility over the former method.</p>\n'}, {'answer_id': 7192, 'author': 'Mike Powell', 'author_id': 205, 'author_profile': 'https://Stackoverflow.com/users/205', 'pm_score': 6, 'selected': False, 'text': '<p>Note that <a href="https://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into#6961">Matt\'s code</a> 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:</p>\n\n<pre><code>DECLARE @EmployeeList varchar(100)\nSELECT @EmployeeList = COALESCE(@EmployeeList + \', \', \'\') + \n    CAST(EmpUniqueID AS varchar(5))\nFROM SalesCallsEmployees\nWHERE SalCal_UniqueID = 1\n</code></pre>\n'}, {'answer_id': 7194, 'author': 'HS.', 'author_id': 618, 'author_profile': 'https://Stackoverflow.com/users/618', 'pm_score': 1, 'selected': False, 'text': '<p>If you\'re running SQL Server 2005, you can write a <a href="https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregates" rel="nofollow noreferrer">custom CLR aggregate function</a> to handle this.</p>\n\n<p>C# version:</p>\n\n<pre><code>using System;\nusing System.Data;\nusing System.Data.SqlClient;\nusing System.Data.SqlTypes;\nusing System.Text;\nusing Microsoft.SqlServer.Server;\n[Serializable]\n[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]\npublic class CSV:IBinarySerialize\n{\n    private StringBuilder Result;\n    public void Init() {\n        this.Result = new StringBuilder();\n    }\n\n    public void Accumulate(SqlString Value) {\n        if (Value.IsNull) return;\n        this.Result.Append(Value.Value).Append(",");\n    }\n    public void Merge(CSV Group) {\n        this.Result.Append(Group.Result);\n    }\n    public SqlString Terminate() {\n        return new SqlString(this.Result.ToString());\n    }\n    public void Read(System.IO.BinaryReader r) {\n        this.Result = new StringBuilder(r.ReadString());\n    }\n    public void Write(System.IO.BinaryWriter w) {\n        w.Write(this.Result.ToString());\n    }\n}\n</code></pre>\n'}, {'answer_id': 1012432, 'author': 'Binoj Antony', 'author_id': 33015, 'author_profile': 'https://Stackoverflow.com/users/33015', 'pm_score': 4, 'selected': False, 'text': "<p>The below code will work for Sql Server 2000/2005/2008</p>\n\n<pre><code>CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)\nRETURNS VARCHAR(1000) AS\nBEGIN\n  DECLARE @csvCities VARCHAR(1000)\n  SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')\n  FROM Vehicles \n  WHERE VehicleId = @VehicleId \n  return @csvCities\nEND\n\n-- //Once the User defined function is created then run the below sql\n\nSELECT VehicleID\n     , dbo.fnConcatVehicleCities(VehicleId) AS Locations\nFROM Vehicles\nGROUP BY VehicleID\n</code></pre>\n"}, {'answer_id': 2226801, 'author': 'JustinStolle', 'author_id': 92389, 'author_profile': 'https://Stackoverflow.com/users/92389', 'pm_score': 2, 'selected': False, 'text': '<p><em>VERSION NOTE: You must be using SQL Server 2005 or greater with Compatibility Level set to 90 or greater for this solution.</em></p>\n\n<p>See this <a href="http://msdn.microsoft.com/en-us/library/ms131056.aspx" rel="nofollow noreferrer" title="Invoking CLR User-Defined Aggregate Functions">MSDN article</a> 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.</p>\n\n<p>My humble recommendation would be to leave out the appended comma so you can use your own ad-hoc delimiter, if any.</p>\n\n<p>Referring to the C# version of Example 1:</p>\n\n<pre><code>change:  this.intermediateResult.Append(value.Value).Append(\',\');\n    to:  this.intermediateResult.Append(value.Value);\n</code></pre>\n\n<p>And</p>\n\n<pre><code>change:  output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);\n    to:  output = this.intermediateResult.ToString();\n</code></pre>\n\n<p>That way when you use your custom aggregate, you can opt to use your own delimiter, or none at all, such as:</p>\n\n<pre><code>SELECT dbo.CONCATENATE(column1 + \'|\') from table1\n</code></pre>\n\n<p><strong>NOTE:</strong> 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."</p>\n'}, {'answer_id': 3098463, 'author': 'John B', 'author_id': 70614, 'author_profile': 'https://Stackoverflow.com/users/70614', 'pm_score': 5, 'selected': False, 'text': '<p>From what I can see <code>FOR XML</code> (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.\nUsing <code>COALESCE(@var...</code> does not allow inclusion of other columns.</p>\n\n<p>Update:\nThanks to <a href="http://blog.programmingsolution.net/sql-server-2008/comma-separated-list-csv-of-rows-of-a-column-using-for-xml-path" rel="noreferrer">programmingsolutions.net</a> there is a way to remove the "trailing" comma to.\nBy making it into a leading comma and using the <code>STUFF</code> function of MSSQL you can replace the first character (leading comma) with an empty string as below:</p>\n\n<pre><code>stuff(\n    (select \',\' + Column \n     from Table\n         inner where inner.Id = outer.Id \n     for xml path(\'\')\n), 1,1,\'\') as Values\n</code></pre>\n'}, {'answer_id': 3672902, 'author': 'teamchong', 'author_id': 442938, 'author_profile': 'https://Stackoverflow.com/users/442938', 'pm_score': 5, 'selected': False, 'text': '<h2>In <a href="http://en.wikipedia.org/wiki/Microsoft_SQL_Server#SQL_Server_2005" rel="noreferrer">SQL Server 2005</a></h2>\n\n<pre><code>SELECT Stuff(\n  (SELECT N\', \' + Name FROM Names FOR XML PATH(\'\'),TYPE)\n  .value(\'text()[1]\',\'nvarchar(max)\'),1,2,N\'\')\n</code></pre>\n\n<hr>\n\n<h2>In SQL Server 2016</h2>\n\n<p>you can use the <a href="https://learn.microsoft.com/en-us/sql/t-sql/queries/select-for-clause-transact-sql" rel="noreferrer">FOR JSON syntax</a></p>\n\n<p>i.e. </p>\n\n<pre><code>SELECT per.ID,\nEmails = JSON_VALUE(\n   REPLACE(\n     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)\n    ,\'"},{"_":"\',\', \'),\'$[0]._\'\n) \nFROM Person per\n</code></pre>\n\n<p>And the result will become</p>\n\n<pre><code>Id  Emails\n1   abc@gmail.com\n2   NULL\n3   def@gmail.com, xyz@gmail.com\n</code></pre>\n\n<p>This will work even your data contains invalid XML characters</p>\n\n<p>the \'"},{"<em>":"\' is safe because if you data contain \'"},{"</em>":"\', it will be escaped to "},{\\"_\\":\\"</p>\n\n<p>You can replace \', \' with any string separator</p>\n\n<hr>\n\n<h2>And in SQL Server 2017, Azure SQL Database</h2>\n\n<p>You can use the new <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql" rel="noreferrer">STRING_AGG function</a></p>\n'}, {'answer_id': 4616468, 'author': 'ZunTzu', 'author_id': 565604, 'author_profile': 'https://Stackoverflow.com/users/565604', 'pm_score': 5, 'selected': False, 'text': "<p>In a single SQL query, without using the FOR XML clause.<br/>\nA Common Table Expression is used to recursively concatenate the results.</p>\n\n<pre><code>-- rank locations by incrementing lexicographical order\nWITH RankedLocations AS (\n  SELECT\n    VehicleID,\n    City,\n    ROW_NUMBER() OVER (\n        PARTITION BY VehicleID \n        ORDER BY City\n    ) Rank\n  FROM\n    Locations\n),\n-- concatenate locations using a recursive query\n-- (Common Table Expression)\nConcatenations AS (\n  -- for each vehicle, select the first location\n  SELECT\n    VehicleID,\n    CONVERT(nvarchar(MAX), City) Cities,\n    Rank\n  FROM\n    RankedLocations\n  WHERE\n    Rank = 1\n\n  -- then incrementally concatenate with the next location\n  -- this will return intermediate concatenations that will be \n  -- filtered out later on\n  UNION ALL\n\n  SELECT\n    c.VehicleID,\n    (c.Cities + ', ' + l.City) Cities,\n    l.Rank\n  FROM\n    Concatenations c -- this is a recursion!\n    INNER JOIN RankedLocations l ON\n        l.VehicleID = c.VehicleID \n        AND l.Rank = c.Rank + 1\n),\n-- rank concatenation results by decrementing length \n-- (rank 1 will always be for the longest concatenation)\nRankedConcatenations AS (\n  SELECT\n    VehicleID,\n    Cities,\n    ROW_NUMBER() OVER (\n        PARTITION BY VehicleID \n        ORDER BY Rank DESC\n    ) Rank\n  FROM \n    Concatenations\n)\n-- main query\nSELECT\n  v.VehicleID,\n  v.Name,\n  c.Cities\nFROM\n  Vehicles v\n  INNER JOIN RankedConcatenations c ON \n    c.VehicleID = v.VehicleID \n    AND c.Rank = 1\n</code></pre>\n"}, {'answer_id': 6178016, 'author': 'Gil', 'author_id': 628972, 'author_profile': 'https://Stackoverflow.com/users/628972', 'pm_score': 3, 'selected': False, 'text': "<p>I've found a solution by creating the following function:</p>\n\n<pre><code>CREATE FUNCTION [dbo].[JoinTexts]\n(\n  @delimiter VARCHAR(20) ,\n  @whereClause VARCHAR(1)\n)\nRETURNS VARCHAR(MAX)\nAS \nBEGIN\n    DECLARE @Texts VARCHAR(MAX)\n\n    SELECT  @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto\n    FROM    SomeTable AS T\n    WHERE   T.SomeOtherColumn = @whereClause\n\n    RETURN @Texts\nEND\nGO\n</code></pre>\n\n<p>Usage:</p>\n\n<pre><code>SELECT dbo.JoinTexts(' , ', 'Y')\n</code></pre>\n"}, {'answer_id': 32391199, 'author': 'Ilya Rudenko', 'author_id': 5299491, 'author_profile': 'https://Stackoverflow.com/users/5299491', 'pm_score': 1, 'selected': False, 'text': "<p>Try this query</p>\n\n<pre><code>SELECT v.VehicleId, v.Name, ll.LocationList\nFROM Vehicles v \nLEFT JOIN \n    (SELECT \n     DISTINCT\n        VehicleId,\n        REPLACE(\n            REPLACE(\n                REPLACE(\n                    (\n                        SELECT City as c \n                        FROM Locations x \n                        WHERE x.VehicleID = l.VehicleID FOR XML PATH('')\n                    ),    \n                    '&lt;/c&gt;&lt;c&gt;',', '\n                 ),\n             '&lt;c&gt;',''\n            ),\n        '&lt;/c&gt;', ''\n        ) AS LocationList\n    FROM Locations l\n) ll ON ll.VehicleId = v.VehicleId\n</code></pre>\n"}, {'answer_id': 37036165, 'author': 'Mike Barlow - BarDev', 'author_id': 92166, 'author_profile': 'https://Stackoverflow.com/users/92166', 'pm_score': 2, 'selected': False, 'text': '<p>With the other answers, the person reading the answer must be aware of the vehicle table and create the vehicle table and data to test a solution.  </p>\n\n<p>Below is an example that uses SQL Server "Information_Schema.Columns" table.  By using this solution, no tables need to be created or data added.  This example creates a comma separated list of column names for all tables in the database.</p>\n\n<pre><code>SELECT\n    Table_Name\n    ,STUFF((\n        SELECT \',\' + Column_Name\n        FROM INFORMATION_SCHEMA.Columns Columns\n        WHERE Tables.Table_Name = Columns.Table_Name\n        ORDER BY Column_Name\n        FOR XML PATH (\'\')), 1, 1, \'\'\n    )Columns\nFROM INFORMATION_SCHEMA.Columns Tables\nGROUP BY TABLE_NAME \n</code></pre>\n'}, {'answer_id': 40010270, 'author': 'nurseybushc', 'author_id': 2255569, 'author_profile': 'https://Stackoverflow.com/users/2255569', 'pm_score': 2, 'selected': False, 'text': "<p>Mun's answer didn't work for me so I made some changes to that answer to get it to work. Hope this helps someone.\nUsing SQL Server 2012:</p>\n\n<pre><code>SELECT [VehicleID]\n     , [Name]\n     , STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,City) \n         FROM [Location] \n         WHERE (VehicleID = Vehicle.VehicleID) \n         FOR XML PATH ('')), 1, 2, '') AS Locations\nFROM [Vehicle]\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/6899', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/799/']