Click here to Skip to main content
15,867,834 members
Articles / Database Development / SQL Server
Article

Concatenate Field Values in One String Using CTE in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.71/5 (14 votes)
6 Nov 2007CPOL4 min read 211.1K   43   21
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:

SQL
-- 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:

SQL
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:

SQL
;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:

SQL
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:

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.

Screenshot - CTECancatString.jpg

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:

SQL
;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:

Screenshot - CTECancatString1.jpg

and the result of the above query is:

Screenshot - CTECancatString2.jpg

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.

License

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


Written By
Technical Lead
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionNice Example! Pin
Leo5622-Dec-14 21:05
Leo5622-Dec-14 21:05 
QuestionWHERE AR.AreaName > CT.AreaName Pin
Member 1021980911-Dec-13 20:33
Member 1021980911-Dec-13 20:33 
GeneralMy vote of 5 Pin
iambics23-Apr-12 9:29
iambics23-Apr-12 9:29 
GeneralMy vote of 5 Pin
vgirimtech2-Oct-11 5:01
vgirimtech2-Oct-11 5:01 
GeneralThe maximum recursion allowed are 100 Pin
sameer11-Jan-10 23:19
professionalsameer11-Jan-10 23:19 
GeneralIf you do not have the Row_Number (SQL Server < 2005) you may use this [modified] Pin
clementratel22-Jan-09 2:51
clementratel22-Jan-09 2:51 
Generalhelp Pin
Member 366481627-Dec-08 3:49
Member 366481627-Dec-08 3:49 
QuestionError message from SQL CTE Query.. Pin
ahmau29-Jan-08 22:05
ahmau29-Jan-08 22:05 
GeneralRe: Error message from SQL CTE Query.. Pin
Navdeep Bhardwaj4-Feb-08 19:35
Navdeep Bhardwaj4-Feb-08 19:35 
AnswerRe: Error message from SQL CTE Query.. Pin
polyconnect16-Aug-11 11:20
polyconnect16-Aug-11 11:20 
GeneralToo bad this solution is not robust at all Pin
muhahahaha9-Jan-08 22:56
muhahahaha9-Jan-08 22:56 
GeneralRe: Too bad this solution is not robust at all Pin
Navdeep Bhardwaj4-Feb-08 19:52
Navdeep Bhardwaj4-Feb-08 19:52 
GeneralRe: Too bad this solution is not robust at all Pin
Member 22045163-Dec-08 22:36
Member 22045163-Dec-08 22:36 
QuestionRe: Too bad this solution is not robust at all Pin
Member 1030017210-Mar-14 4:50
Member 1030017210-Mar-14 4:50 
AnswerRe: Too bad this solution is not robust at all Pin
Member 1030017210-Mar-14 6:37
Member 1030017210-Mar-14 6:37 
GeneralExcellent Article Pin
ShirleySW27-Nov-07 4:44
ShirleySW27-Nov-07 4:44 
GeneralRe: Excellent Article Pin
caradens17-Oct-08 14:21
caradens17-Oct-08 14:21 
GeneralNice article however... Pin
Eyal Lantzman6-Nov-07 3:10
Eyal Lantzman6-Nov-07 3:10 
GeneralI agree - extending the article Pin
Mike Ellison29-Oct-07 5:43
Mike Ellison29-Oct-07 5:43 
GeneralI like this, but I'd love to see the article extended a bit. Pin
Ashaman29-Oct-07 3:03
Ashaman29-Oct-07 3:03 
GeneralRe: I like this, but I'd love to see the article extended a bit. Pin
Navdeep Bhardwaj29-Oct-07 3:36
Navdeep Bhardwaj29-Oct-07 3:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.