How to Concatenate Column Values Using T-SQL

Sometimes you would want to concatenate column values in T-SQL and there are different ways to achieve that. Consider the two tables below:

+--------+---------------+
| MakeId |          Name |
+--------+---------------+
|      1 |           BMW |
|      2 | Mercedes-Benz |
|      3 |          Audi |
+--------+---------------+

+---------+--------+---------------+
| ModelId | MakeId |          Name |
+---------+--------+---------------+
|       1 |      3 |            A3 |
|       2 |      3 |            A5 |
|       3 |      3 |            A6 |
|       4 |      3 |            A4 |
|       5 |      3 |            A8 |
|       6 |      1 |            Z4 |
|       7 |      1 |            M5 |
|       8 |      1 |            M3 |
|       9 |      2 |          E550 |
|      10 |      2 |          C500 |
|      11 |      2 |          E350 |
+---------+--------+---------------+

If I wanted to get a list of all makes, one of the popular approaches to do that is to use the built-in function called COALESCE:

DECLARE @Makes VARCHAR(500)
SET @Makes = NULL
SELECT @Makes = COALESCE(@Makes + ', ', '') + Makes.Name
	FROM Makes
	ORDER BY Makes.Name
SELECT @Makes

This will return me a result set consisting of one row:

Audi, BMW, Mercedes-Benz

However, I need to declare a variable in order to use it. And now, what if I wanted to get a result set of all the makes with a comma-separated list of their models? Sure I could easily take care of this in my code but sometimes you just want to return it right in the SQL. For instance, if you are binding your result set to a DataGrid in C#. Another way, starting with MS-SQL 2005, is to use FOR XML PATH() to do just that and more complex queries:

SELECT Makes.Name AS Make,
		STUFF
		(
			(
				SELECT ', ' + Models.Name
					FROM Models 
					WHERE Models.MakeId = Makes.MakeId
					ORDER BY Models.Name
					FOR XML PATH('')
			), 1, 1, ''
		) AS [Available Models]
	FROM Makes
	ORDER BY Makes.Name

Now, this piece of code will produce the following result set:

+---------------+--------------------+
|          Make |   Available Models |
+---------------+--------------------+
|          Audi | A3, A4, A5, A6, A8 |
|           BMW |         M3, M5, Z4 |
| Mercedes-Benz |   C500, E350, E550 |
+---------------+--------------------+

Neat, right?

Play around with it yourself, just launch Microsoft SQL Server Management Studio and copy over this code as a start:

IF OBJECT_ID('#Makes') IS NOT NULL DROP TABLE #Makes
IF OBJECT_ID('#Models') IS NOT NULL DROP TABLE #Models

CREATE TABLE #Makes
(
	MakeId INT,
	Name VARCHAR(50)
)

CREATE TABLE #Models
(
	ModelId INT,
	MakeId INT,
	Name VARCHAR(50)
)

INSERT INTO #Makes (MakeId, Name) VALUES (1, 'BMW')
INSERT INTO #Makes (MakeId, Name) VALUES (2, 'Mercedes-Benz')
INSERT INTO #Makes (MakeId, Name) VALUES (3, 'Audi')

INSERT INTO #Models (ModelId, MakeId, Name) VALUES (1, 3, 'A3')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (2, 3, 'A4')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (3, 3, 'A5')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (4, 3, 'A6')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (5, 3, 'A8')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (6, 1, 'Z4')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (7, 1, 'M5')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (8, 1, 'M3')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (9, 2, 'E550')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (10, 2, 'C500')
INSERT INTO #Models (ModelId, MakeId, Name) VALUES (11, 2, 'E350')

DECLARE @Makes VARCHAR(500)
SET @Makes = NULL
SELECT @Makes = COALESCE(@Makes + ', ', '') + #Makes.Name
	FROM #Makes
	ORDER BY #Makes.Name
SELECT @Makes

SELECT #Makes.Name AS Make,
		STUFF
		(
			(
				SELECT ', ' + #Models.Name
					FROM #Models 
					WHERE #Models.MakeId = #Makes.MakeId
					ORDER BY #Models.Name
					FOR XML PATH('')
			), 1, 1, ''
		) AS [Available Models]
	FROM #Makes
	ORDER BY #Makes.Name


DROP TABLE #Makes
DROP TABLE #Models
This entry was posted in MSSQL and tagged , , , , , , , , . Bookmark the permalink.

One Response to How to Concatenate Column Values Using T-SQL

  1. Thanks for an idea, you sparked at thought from a angle I hadn’t given thoguht to yet. Now lets see if I can do something with it.

Leave a Reply

Your email address will not be published. Required fields are marked *