One of the shortcomings of SQL is that you can’t easily perform a SUM over a text field, packing up the values, and there are many situations when we want to concatenate the multiple text values into a string.

In this article we will look at how to achieve this using a Common Table Expression (CTE), we will use the Products table in Northwind, and set the challenge to concatenate together all the Product Names in each product category.

CTE expressions consist of two parts, the part that gets the parent records, and the part that gets the child records, and the child records can recursively use the expression to get more dependent records.

  ;WITH CTE_Products AS (
    -- This defines the parent record
  UNION ALL
    -- This defines the child record
    -- and can refer back to the CTE_Products object
    -- to generate recursion
  )

What you may not immediately spot is that CTE expressions are very useful in situations where you may not have a hierarchy of data, but simply used as a clever technique for assembling records. In our example we are going to use a temporary table, if you have a field which gathers and orders records sequentially then you can avoid the need to create a temporary table.

To better understand how to use this technique, we will start with an example that packs up a set of product names for a single category, and then we will show a more generalised example.

Packing up text fields

We are starting with a table that contains a list of product names, each assigned to a category.

The result we want to obtain for Category = 1, is shown below:-

We start by creating a temporary table which will hold the list of product names for category =1, ordered by the sequential key AnId.

  CREATE TABLE #TempResults(
    AnID INT IDENTITY(1,1),
    ProductName VARCHAR(40)
  )

  INSERT INTO #TempResults(ProductName)
  SELECT ProductName
  FROM Products
  WHERE CategoryID = 1
  ORDER BY ProductName

If we start by just executing the SQL which gets the parent record, then that is as follows:-

  SELECT TOP 1 p.AnID,
  CONVERT(VARCHAR(1024),ProductName) As ProductName
  FROM
    #TempResults p

This returns the following single row:-

If we were to execute the following CTE Expression:-

  ;WITH CTE_Products AS (
    -- This defines the parent record
    SELECT TOP 1 p.AnID,
    CONVERT(VARCHAR(1024),ProductName) As ProductName
    FROM
      #TempResults p
    UNION ALL
    -- This defines the child record
    SELECT c.AnID, c.ProductName
    FROM
      #TempResults c
  )

  SELECT * FROM CTE_Products

Then we would see a union between the parent and all the child related records:-

But we want to process the child records one at a time concatenating the product names, so we need the following expression (notice that the child part is recursive and makes a reference to CTE_Products):-

  ;WITH CTE_Products AS (
      -- This defines the parent record
      SELECT TOP 1 p.AnID,
      CONVERT(VARCHAR(1024),ProductName) As ProductName
      FROM
        #TempResults p
    UNION ALL
      -- This defines the child record
      SELECT c.AnID,
      CONVERT(VARCHAR(1024),p.ProductName + CHAR(13) + CHAR(10)
      + c.ProductName) As ProductName
      FROM
        #TempResults c
          INNER JOIN CTE_Products p
          ON c.AnID = p.AnID+1
  )

  SELECT * FROM CTE_Products

In the above code we used the TOP function to return the first record.

This results in the following records:-

By altering the select to the following:-

  SELECT TOP 1 *
  FROM CTE_Products
  ORDER BY AnID DESC

We can return the one desired record:-

Returning multiple results

The next example shows how to generalise the above to return a record for each available category, which returns the following result:-

To do this we use the following:-

  IF OBJECT_ID('tempdb..#TempResults') IS NOT NULL DROP TABLE #TempResults

  CREATE TABLE #TempResults(
    AnID INT IDENTITY(1,1),
    ProductName VARCHAR(40),
    CategoryID INT
  )

  INSERT INTO #TempResults(ProductName, CategoryID)
  SELECT ProductName, CategoryID
  FROM Products
  ORDER BY CategoryID, ProductName

  ;WITH CTE_Products AS (
      -- This defines the parent record
      SELECT p.AnID, p.CategoryID,
      CONVERT(VARCHAR(1024),ProductName) As ProductName
      FROM
        #TempResults p
          INNER JOIN
            (SELECT MIN(p.AnID) As MinAnID, p.CategoryID
            FROM
              #TempResults p
            GROUP BY p.CategoryID ) As FirstInEachGroup
            ON p.AnID = FirstInEachGroup.MinAnID
  UNION ALL
      -- This defines the child record
      SELECT c.AnID, c.CategoryID,
      CONVERT(VARCHAR(1024),p.ProductName + CHAR(13) + CHAR(10)
      + c.ProductName) As ProductName
      FROM
        #TempResults c
          INNER JOIN CTE_Products p
            ON c.AnID = p.AnID+1
            AND c.categoryID = p.categoryID
  )

  SELECT CategoryID , ProductName, AnID
  FROM CTE_Products cte
  WHERE AnID = (SELECT Max(t.AnID) AS MaxAnID
            FROM #TempResults t
            WHERE t.CategoryID = cte.categoryID)

Simplifying the process

If your table already has a sequential key, the you do not need to create a temporary table, but can develop the CTE against your physical table, this also means that you can create a function to return the packed text fields.

Download Code

The sample TSQL can be downloaded from here.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page