考虑一个包含名称的数据库表,其中有三行:

Peter
Paul
Mary

有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?


当前回答

提出了递归CTE解决方案,但没有提供代码。下面的代码是递归CTE的示例。

请注意,虽然结果与问题相符,但数据与给定的描述并不完全相符,因为我假设您确实希望对行组(而不是表中的所有行)执行此操作。将其更改为与表中的所有行相匹配是读者的练习。

;WITH basetable AS (
    SELECT
        id,
        CAST(name AS VARCHAR(MAX)) name,
        ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
        COUNT(*) OVER (Partition BY id) recs
    FROM (VALUES
        (1, 'Johnny', 1),
        (1, 'M', 2),
        (2, 'Bill', 1),
        (2, 'S.', 4),
        (2, 'Preston', 5),
        (2, 'Esq.', 6),
        (3, 'Ted', 1),
        (3, 'Theodore', 2),
        (3, 'Logan', 3),
        (4, 'Peter', 1),
        (4, 'Paul', 2),
        (4, 'Mary', 3)
    ) g (id, name, seq)
),
rCTE AS (
    SELECT recs, id, name, rw
    FROM basetable
    WHERE rw = 1

    UNION ALL

    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
    FROM basetable b
    INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
OPTION (MAXRECURSION 101)

其他回答

Oracle有两种方法:

    create table name
    (first_name varchar2(30));

    insert into name values ('Peter');
    insert into name values ('Paul');
    insert into name values ('Mary');

解决方案是1:

    select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
    o/p=> Peter,Paul,Mary

解决方案是2:

    select  rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
    o/p=> Peter,Paul,Mary

在Oracle中,它是wm_concat。我相信这个功能在10g及更高版本中可用。

SELECT PageContent = Stuff(
    (   SELECT PageContent
        FROM dbo.InfoGuide
        WHERE CategoryId = @CategoryId
          AND SubCategoryId = @SubCategoryId
        for xml path(''), type
    ).value('.[1]','nvarchar(max)'),
    1, 1, '')
FROM dbo.InfoGuide info

我真的很喜欢Dana回答的优雅,只想让它变得完整。

DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

Oracle 11g Release 2支持LISTAGG功能。此处的文档。

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

警告

如果生成的字符串可能超过4000个字符,请小心执行此函数。它将抛出异常。如果是这种情况,那么您需要处理异常或滚动自己的函数,以防止连接的字符串超过4000个字符。