Simplify CTE string concatenation?

Question

I have the following code list

Code Meaning 
1    Single 
2    Married/Separate 
3    Divorced 
4    Widowed 
8    Not Applicable 
99   Not known

I am trying to flatten these into a single row using a CTE. I have a CTE solution which uses RowNumber function to do this.

WITH Flattened (JoinItem, CodeMeaning) AS
(
    SELECT 1 AS JoinItem, CAST('' AS VARCHAR(255))
    UNION ALL
    SELECT f.JoinItem+1, CAST(f.CodeMeaning + ',' + c.CodeMeaning AS VARCHAR(255))
    FROM
    (
        SELECT JoinItem = ROW_NUMBER() OVER (ORDER BY Code),c.Code + ' - ' + c.Meaning AS CodeMeaning
        FROM Codes c
    ) c
    INNER JOIN Flattened f
    ON f.JoinItem=c.JoinItem
)
SELECT TOP 1 JoinItem,  CodeMeaning 
FROM Flattened 
ORDER BY JoinItem DESC

However, I'm wondering if I can do it without using the RowNumber function but still using a CTE. So I have the following - what I view as simpler - Sql

WITH Flattened (JoinItem, CodeMeaning) AS
(
    SELECT 1 AS JoinItem, CAST('' AS VARCHAR(255))
    UNION ALL
    SELECT c.JoinItem, CAST(f.CodeMeaning + ',' + c.CodeMeaning AS VARCHAR(255))
    FROM
    (
        SELECT 1 AS JoinItem,c.Code + ' - ' + c.Meaning AS CodeMeaning
        FROM Codes c            
    ) c
    INNER JOIN Flattened f
    ON f.JoinItem=c.JoinItem
)
SELECT JoinItem, odeMeaning 
FROM Flattened

Now it is max-ing out on recursion and generating something like a cartesian join - if not worse!

I'm looking to try and get it to join to the anchor record each time using a fixed "JoinItem"

So any pointers to where I am going wrong would be helpful if there is a solution.

EDIT SqlFiddle

possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Bulat, Sep 15 '14 at 12:30
You are likely to find an answer here - http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Bulat, Sep 15 '14 at 12:30

Gordon Linoff · Accepted Answer · 2014-09-15T12:33:49.813

5

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.

edited Sep 15 '14 at 12:33

answered Sep 15 '14 at 12:20

Gordon Linoff

1,242,037
58
646
786

thx vm again Gordon. Is there any chance you could explain why my version - specifically having a fixed JoinItem in the anchor and joining to that in the recursive member - doesn't work? I'm obviously misunderstanding the mechanics of CTEs. Thx again. – Simon Woods Sep 16 '14 at 06:16
@SimonWoods . . . Your code seems to work: http://www.sqlfiddle.com/#!6/d41d8/21618. – Gordon Linoff Sep 16 '14 at 11:40
yes thx Gordon ... I was wondering if I could avoid using the ROW_NUMBER function and just use a "fixed" JoinItem value in some way instead, so that the anchor runs and then the recursive part appends to that anchor based on the fixed JoinItem. (Hence the second bit of Sql in my q). Thx again for your time. – Simon Woods Sep 17 '14 at 06:49

Aleksey · Answer 2 · 2017-12-17T19:00:48.467

Without the ROW_NUMBER() your query should be

WITH Flattened (Code, CodeMeaning, lev) AS
(
    SELECT TOP 1 Code, CAST(CAST(Code AS VARCHAR(255)) + ' - ' + Meaning AS VARCHAR(255)),  0 AS lev FROM codes ORDER BY Code    
    UNION ALL
    SELECT c.Code, CAST(f.CodeMeaning + ',' + CAST(c.Code AS varchar(255))+ ' - ' + c.Meaning AS VARCHAR(255)), f.lev+1
    FROM codes c
    INNER JOIN Flattened f
    ON c.Code > f.Code
)
SELECT TOP 1 CodeMeaning  FROM Flattened ORDER BY lev DESC;

You can also use ORDER BY LEN(CodeMeaning) DESC in the last select. In that case the lev (recursion level) column is not necessary. If you have an extra knowledge about Code values then you can optimize the query using more accurate INNER JOIN predicate like ON f.Code < c.Code AND f.Code+10 > c.Code.

Simplify CTE string concatenation?

2 Answers2

Linked