是否可以使用CTE将列值连接成字符串？ [英] Is it possible to concatenate column values into a string using CTE?

问题描述

说我有下表：

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

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

Is it possible to use a recursive CTE to generate the following output:

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

我已经玩了一下，但是还没买到加工。我会使用其他技术做得更好吗？

I've played around with it a bit but haven't been able to get it working. Would I do better using a different technique?

推荐答案

我不推荐这样做，但我设法解决了。

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

表：

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方法：

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

这篇关于是否可以使用CTE将列值连接成字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！