Simplify CTE string concatenation?

问题:

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

回答1:

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.

回答2:

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.