简化 CTE 字符串连接? [英] Simplify CTE string concatenation?

问题描述

我有以下代码列表

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

我正在尝试使用 CTE 将这些扁平化为一行.我有一个 CTE 解决方案，它使用 RowNumber 函数来执行此操作.

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

但是，我想知道是否可以在不使用 RowNumber 函数但仍使用 CTE 的情况下做到这一点.所以我有以下 - 我认为更简单 - Sql

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!

我正在尝试让它每次使用固定的JoinItem"加入锚记录

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.

编辑 SqlFiddle

推荐答案

假设这是SQL Server，你有没有考虑过这样的事情:

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, '')

要使用 CTE 执行此操作，请先定义序号，然后进行展平:

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.

这篇关于简化 CTE 字符串连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！