Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Stupid CTE tricks -- string concatenation

4.67/5 (5 votes)
25 Dec 2011CPOL 40.7K  
Concatenating values from multiple rows into one string value via a Common Table Expression
If you have a table like:

Make    Model
------- -----------
Toyota  Yaris
Toyota  Corrolla
Ford    Pinto
Ford    Model A
Ford    Thunderbird
Nash    Rambler


and you want something like:

Make    Models                          Count
------- ------------------------------- -----
Ford    Thunderbird , Pinto , Model A   3
Nash    Rambler                         1
Toyota  Yaris , Corrolla                2


you'll need to write some code -- SQL Server doesn't have that built in (yet).
You can write a function to do it, you can write a custom CLR aggregator to do it, but I wanted to see if I could use a Common Table Expression to do it.

The following is such a CTE:

WITH cte AS
(
  SELECT Make
  , COUNT(*) [Count]
  , MAX(Model) Models
  , 0 [Rank]
  FROM MakeModel
  GROUP BY Make
UNION ALL
  SELECT cte.Make
  , cte.[Count]
  , Models + N' , ' + Model
  , [Rank] + 1
  FROM cte
  INNER JOIN MakeModel
  ON cte.Make=MakeModel.Make
  AND cte.Models NOT LIKE '%' + Model + '%'
  AND cte.[Rank]<cte.[Count]
)
 
SELECT Make,MAX(Models) Models,MAX([Count]) [Count] FROM cte GROUP BY Make ORDER BY Make


Caveats:
0) You may need to CONVERT your values to NVARCHAR(MAX)
1) You may need to protect against NULL values
2) I don't think this will scale well
3) I can't think of a way to get the values listed ascending rather than descending

This was mainly an interesting exercise, I don't expect to use it in production systems.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)