Concatenate Field Values in One String Using CTE in SQL Server

Introduction

While I was studying the new feature of SQL Server 2005, i.e., CTE, I got an idea to use it to concatenate the values of a field in one string, since it can be used to work recursively. Before starting up with the example, let me first describe about the CTE.

CTE or Common Table Expression is a new construct provided in MS SQL Server 2005. It is basically a temporary view that can be used in SELECT statements to query data. Most of the time, we need to write complex queries involving some subquery being used multiple times in a single query. In that case, we can use a CTE and reference it in the query as many times as required. This simplifies the logic of the query, and makes it more maintainable.

The syntax for creating a CTE is:

-- Defining a CTE

;WITH SalesmanCTE(SalesmanId, SalesmanName)
AS
(
    SELECT SalesmanId, Name FROM Salesman
)


-- Refering a CTE in query

SELECT * FROM SalesmanCTE

The CTE can be used very much like normal views created in the database, so we can directly embed some complex query (that might be required to be used as a subquery) in the CTE and refer that CTE in our query. We can use any kind of join, where clause, or other constructs as can be used with a normal table or query. For example, let's say we need to write a query like:

SELECT SalesmanName
FROM Salesman 
INNER JOIN (SELECT SalesmanId, MAX(Sale) FROM Sales GROUP BY SalesmanId) A
ON Salesman.SalesmanId = A.SalesmanId

This can be easily written with CTE:

;WITH SalesmanCTE (SalesmanId, MaxSale)
AS
(
  SELECT SalesmanId, MAX(Sale) 
  FROM Sales 
  GROUP BY SalesmanId
)

SELECT SalesmanName
FROM Salesman 
INNER JOIN SalesmanCTE A
ON Salesman.SalesmanId = A.SalesmanId

In the second example, the subquery has been modified as a separate view using CTE, and used in the main query, making it easy to understand. The CTE has another good feature of recursive calling, i.e., a CTE can call itself recursively to return hierarchical data. For example, if you have a table of recursive nature, like category that has a self referential foreign key constraint to represent n level categories (something that we see in shopping carts etc.). Here, to get all the children (up to n level) of a category, we can use CTE. More information on how to use it recursively can be found here.

I am using the same recursive nature of CTE in this article to concatenate the values of rows as a comma separated value into a column. A basic example is given below.

Using the code

For this, I have used the following table:

tblTest
------------------
FId INT
FName VARCHAR(10)

The values in the table are:

FId FName
--- ----
2    A
4    B
5    C
6    D
8    E

The SQL:

;WITH ABC (FId, FName) AS
(
    SELECT 1, CAST('' AS VARCHAR(8000)) 
    UNION ALL
    SELECT B.FId + 1, B.FName +  A.FName + ', ' 
    FROM (And the above query will return
SELECT Row_Number() OVER (ORDER BY FId) AS RN, FName FROM tblTest) A 
    INNER JOIN ABC B ON A.RN = B.FId 
)
SELECT TOP 1 FName FROM ABC ORDER BY FId DESC

And the above query will return:

FName
----------------------------
A, B, C, D, E,

Here in CTE, the first query runs first, and the second query runs recursively to concatenate the field values in a common string field. And the last query just shows the last row of the resultant resultset of the CTE.

I am now taking up a more real life problem that can be solved using this. The suggestion for this example was given by Ashaman, who was the first one to comment on this article. So, taking up that example, I am taking three tables as shown in the relationship diagram below.

The Salesman table contains the names of salesmen working for the company. The Area table keeps the areas where the products are being sold, and SalesmanArea keeps the information of which salesmen work under which area. A salesman can be working for multiple locations. Now, let's say we have a requirement that we want to show the names of all the salesmen along with the comma separated list of areas that are being supervised by them. To get such a result, we can use the recursive feature of CTE, and the query will be:

;WITH AreaCTE (RowNumber, SalesmanId, AreaName, Areas) AS
(
  SELECT 1, SA.SalesmanId, MIN(AR.AreaName), CAST(MIN(AR.AreaName) AS VARCHAR(8000)) 
  FROM SalesmanArea SA
  INNER JOIN Area AR ON SA.AreaId = AR.AreaId
  GROUP BY SalesmanId

  UNION ALL

  SELECT CT.RowNumber + 1, SA.SalesmanId, AR.AreaName, CT.Areas + ', ' + AR.AreaName 
  FROM SalesmanArea SA 
  INNER JOIN Area AR ON SA.AreaId = AR.AreaId
  INNER JOIN AreaCTE CT ON CT.SalesmanId = SA.SalesmanId 
  WHERE AR.AreaName > CT.AreaName
)

SELECT A.SalesmanId, S.Name, Areas 
FROM AreaCTE A
INNER JOIN Salesman S ON S.SalesmanId = A.SalesmanId
INNER JOIN (SELECT SalesmanId, MAX(RowNumber) 
AS MaxRow FROM AreaCTE GROUP BY SalesmanId) R
ON A.RowNumber = R.MaxRow AND A.SalesmanId = R.SalesmanId
ORDER BY SalesmanId

The records that my table contains are:

and the result of the above query is:

This gives me the required result with the name of a salesman and the comma separated list of areas under which he is active. When we write a recursive CTE, we need to provide two queries that are joined together with a UNION ALL. The first query is called the anchor query/member, and the second one is called the recursive query/member. First, the first query is fired and the result of it is used by the second query to generate its results since the second query is referencing the CTE itself. This way, CTE calls itself to give recursive processing of the data.

Hope this helps in getting an idea of CTE.

History

1. Created article with a basic example.
2. Added more information on CTE.
3. Added a real life example of a Salesman and Area problem as per the suggestions.