How to Concatenate Column Values into a String Using Cte

Is it possible to concatenate column values into a string using CTE?

I do not recommend this, but I managed to work it out.

Table:

CREATE TABLE [dbo].[names](
    [id] [int] NULL,
    [myId] [int] NULL,
    [name] [char](25) NULL
) ON [PRIMARY]

Data:

INSERT INTO names values (1,3,'Bob')
INSERT INTO names values 2,3,'Chet')
INSERT INTO names values 3,3,'Dave')
INSERT INTO names values 4,4,'Jim')
INSERT INTO names values 5,4,'Jose')
INSERT INTO names values 6,5,'Nick')

Query:

WITH CTE (id, myId, Name, NameCount)
     AS (SELECT id,
                myId,
                Cast(Name AS VARCHAR(225)) Name,
                1                          NameCount
         FROM   (SELECT Row_number() OVER (PARTITION BY myId ORDER BY myId) AS id,
                        myId,
                        Name
                 FROM   names) e
         WHERE  id = 1
         UNION ALL
         SELECT e1.id,
                e1.myId,
                Cast(Rtrim(CTE.Name) + ',' + e1.Name AS VARCHAR(225)) AS Name,
                CTE.NameCount + 1                                     NameCount
         FROM   CTE
                INNER JOIN (SELECT Row_number() OVER (PARTITION BY myId ORDER BY myId) AS id,
                                   myId,
                                   Name
                            FROM   names) e1
                  ON e1.id = CTE.id + 1
                     AND e1.myId = CTE.myId)
SELECT myID,
       Name
FROM   (SELECT myID,
               Name,
               (Row_number() OVER (PARTITION BY myId ORDER BY namecount DESC)) AS id
        FROM   CTE) AS p
WHERE  id = 1 

As requested, here is the XML method:

SELECT myId,
       STUFF((SELECT ',' + rtrim(convert(char(50),Name))
        FROM   namestable b
        WHERE  a.myId = b.myId
        FOR XML PATH('')),1,1,'') Names
FROM   namestable a
GROUP BY myId

Concatenating columns using CTE in SQL Server 2008

Try this one -

DDL:

IF OBJECT_ID (N'dbo.TABLEX') IS NOT NULL
   DROP TABLE TABLEX

IF OBJECT_ID (N'dbo.X001') IS NOT NULL
   DROP TABLE X001

IF OBJECT_ID (N'dbo.X002') IS NOT NULL
   DROP TABLE X002

CREATE TABLE dbo.TABLEX (NAME VARCHAR(50), TABLE_NAME VARCHAR(50))
INSERT INTO dbo.TABLEX (NAME, TABLE_NAME)
VALUES ('X1', 'X001'), ('X2', 'X002')

CREATE TABLE dbo.X001 (X1_A VARCHAR(50), X1_B VARCHAR(50))
CREATE TABLE dbo.X002 (X2_A VARCHAR(50), X2_B VARCHAR(50))

Query:

;WITH cte AS
(
     SELECT 
            NAME
          , TABLE_NAME
          , [COLUMN] = CAST('' AS VARCHAR(1024))
          , POS = 1
     FROM TABLEX t

     UNION ALL

     SELECT 
            t.NAME
          , t.TABLE_NAME
          , CAST([COLUMN] + ', ' + c.name AS VARCHAR(1024))
          , POS + 1
     FROM cte t
     JOIN sys.columns c ON 
                 OBJECT_ID('dbo.' + t.TABLE_NAME) = c.[object_id] 
               AND 
                 t.POS = c.column_id
)
SELECT 
       NAME
     , TABLE_NAME
     , [COLUMNS] = STUFF([COLUMN], 1, 2, '')
FROM (
     SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY POS DESC)
     FROM cte
) t
WHERE t.rn = 1

Results:

NAME   TABLE_NAME    COLUMNS
------ ------------- -------------
X1     X001          X1_A, X1_B
X2     X002          X2_A, X2_B

Query cost:

Statistic:

    Query Presenter  Scans  Logical Reads
-------------------  -----  -------------
                XML      5              9
                CTE      3             48

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

Simplify CTE string concatenation?

Assuming this is SQL Server, have you considered something like this:

select stuff((select ',' + c.code + '-' + c.Meaning
              from codes c
              order by code
              for xml path ('')
             ), 1, 1, '')

EDIT:

To do this with a CTE, define the sequential numbers first and then do the flattening:

with c as (
      select row_number() over (order by code) as seqnum, c.code + '-' + c.meaning as CodeMeaning
      from codes c
     ),
     flattened as (
      select CodeMeaning as CodeMeaning
      from c
      where rownum = 1
      union all
      select f.CodeMeaning + ',' + c.CodeMeaning
      from c join
           flattened f
           on c.seqnum = f.seqnum + 1
     )
select *
from flattened;

You might have to increase the default recursion level if your list is too long.

SQL recursion + column concatenation

You can use a recursive CTE:

with cte as (
      select id, convert(varchar(max), relativeurl) as url, 1 as lev
      from page
      where parentid is null
      union all
      select p.id, concat(cte.url, p.relativeurl), lev + 1
      from cte join
           page p
           on p.parentid = cte.id
     )
select cte.*
from cte;

Here is a db<>fiddle.

Recursive CTE concatenate fields with parents from arbitrary point

In the top-down method the initial query should select only roots (items without parents), so the query returns each row only once:

with recursive top_down as (
    select id, parent, text
    from test
    where parent is null
union all
    select t.id, t.parent, concat_ws('/', r.text, t.text)
    from test t
    join top_down r on t.parent = r.id
)
select id, text
from top_down
where id = 4    -- input

If your goal is to find a specific item, the bottom-up approach is more efficient:

with recursive bottom_up as (
    select id, parent, text
    from test
    where id = 4    -- input
union all
    select r.id, t.parent, concat_ws('/', t.text, r.text)
    from test t
    join bottom_up r on r.parent = t.id
)
select id, text
from bottom_up
where parent is null

Remove final where conditions in both queries to see the difference.

Test it in rextester.

How to loop, search and concatenate string in with cursor in TSQL?

Any time you think "I should loop" in a database you should immediately think "but there must be a better "set" approach to solving this".

A recursive CTE is the better "set" approach to solving this. Check out Example D in that link in the "Examples" section of the page.

WITH buildstring As
(
    /*
     * Recursive Seed - Starting point of recursive lookup
     */
    SELECT psrid, parent_psrid, description, 1 as depth
    FROM yourtable
    WHERE psrid = 10

    UNION ALL

    /* 
     * Recursive Member - The part that loops until the join(s) fail
     * Note that we refer to the CTE itself (buildstring) in the 
     * FROM clause, which is what causes the recursiveness. 
     */
    SELECT
        yourtable.psrid,
        yourtable.parent_psrid,
        buildstring.description + " " + yourtable.description,
        buildstring.depth + 1
    FROM buildstring
        INNER JOIN yourtable 
            ON buildstring.parent_psrid = yourtable.psrid
    WHERE depth < 20 --Just in case you get into an infinite cycle
          
)
SELECT * 
FROM (SELECT description, row_number() OVER (ORDER BY depth DESC) as rn FROM buildstring) depth
WHERE rn = 1 --Get the last record created by the recursive CTE

That should get you in the ballpark. You'll most likely want to tweak that a bit to get whatever output you are looking for, but that should spit out a description with all parent descriptions of psrid of 10.

---

Related Topics