是否可以使用CTE将列值连接成字符串？

一尘不染

是否可以使用CTE将列值连接成字符串？

sql

说我有下表：

id|myId|Name
-------------
1 | 3  |Bob 
2 | 3  |Chet
3 | 3  |Dave
4 | 4  |Jim
5 | 4  |Jose
-------------

是否可以使用递归CTE生成以下输出：

3 | Bob, Chet, Date
4 | Jim, Jose

我已经试了一下，但是还没能使它正常工作。我会使用其他技术做得更好吗？

阅读 169

2021-03-10

共1个答案

一尘不染

我不建议这样做，但是我设法解决了。

桌子：

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

数据：

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

询问：

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

根据要求，这是XML方法：

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

2021-03-10