Sometimes when generating reports or text for web pages or form letters, it becomes handy to concatenate multiple column values from rows of data into a single value entirely within T-SQL. In this article, we will explore two different approaches to achieve this in SQL Server.

Solution 1: Using Recursive Common Table Expressions (CTE)

The first solution involves using common table expressions (CTE) and, in particular, a recursive CTE. Let’s consider a table called FRUIT with columns id and name:

id   name
101  Apple
102  Banana
103  Orange
104  Melon
105  Grape

The goal is to select rows from this table and efficiently create a single result such as: “Apple, Banana, Orange, Melon, Grape”.

To achieve this, we can use a recursive CTE. The first CTE assigns a unique and sequential row number to each row value:

WITH list AS (
  SELECT
    name AS value,
    ROW_NUMBER() OVER (ORDER BY name) AS num
  FROM
    FRUIT
)

The second CTE is recursive and builds up the concatenated values as recursion progresses:

WITH concatenations AS (
  -- non-recursive section
  SELECT
    value AS head,
    CAST('' AS VARCHAR(MAX)) AS tail,
    num
  FROM
    list
  UNION ALL
  -- recursive section
  SELECT
    head,
    (SELECT value FROM list WHERE num = prev.num - 1) + ', ' + tail AS tail,
    num - 1
  FROM
    concatenations AS prev
  WHERE
    num > 0
)

The third and final CTE selects only the desired result:

WITH concatenated AS (
  SELECT
    MAX(tail + head) AS items
  FROM
    concatenations
  WHERE
    num = 1 
)

After executing the above queries, the result will be “Apple, Banana, Orange, Melon, Grape”.

Solution 2: Using FOR XML PATH Expression

Another option to concatenate multiple column values is by using the FOR XML PATH expression. This approach does not require CTEs and has a more compact syntax:

SELECT 
  REPLACE(
    REPLACE(
      REPLACE(
        (SELECT name FROM FRUIT ORDER BY id FOR XML PATH('')),
        '',
        ', ' -- delimiter
      ),
      '',
      ''
    ),
    '',
    ''
  )

After executing the above query, the result will be the same as in Solution 1: “Apple, Banana, Orange, Melon, Grape”.

Conclusion

Both solutions presented in this article allow you to efficiently concatenate multiple column values into a single string in SQL Server. Solution 1, using recursive CTEs, provides more flexibility and control over the concatenation process. On the other hand, Solution 2, using the FOR XML PATH expression, offers a simpler syntax but may have unpredictable performance in certain scenarios.

It’s important to consider the specific requirements and performance characteristics of your application when choosing the appropriate method for concatenating column values in SQL Server.

Stay tuned for future articles where we will explore more SQL Server concepts and techniques.