我可以用逗号将多行分隔为一列吗? [英] Can I Comma Delimit Multiple Rows Into One Column?

问题描述

我正在尝试在我的 SQL Server 数据库中合并类似的内容:

I am attempting to merge something like this in my SQL Server database:

[TicketID], [Person]
 T0001       Alice
 T0001       Bob
 T0002       Catherine
 T0002       Doug
 T0003       Elaine

进入这个:

[TicketID], [People]
 T0001       Alice, Bob
 T0002       Catherine, Doug
 T0003       Elaine

我需要在 SQL Server 和 Oracle 中执行此操作.

I need to do this in both SQL Server and Oracle.

我找到了 MySQL 的 GROUP_CONCAT 函数，它完全满足我的需求，但 MySQL 不是这里的一个选项.

I have found the function GROUP_CONCAT for MySQL that does exactly what I need here, but MySQL is not an option here.

测试台:

DECLARE @Tickets TABLE (
    [TicketID] char(5) NOT NULL,
    [Person] nvarchar(15) NOT NULL
)

INSERT INTO @Tickets VALUES
    ('T0001', 'Alice'),
    ('T0001', 'Bob'),
    ('T0002', 'Catherine'),
    ('T0002', 'Doug'),
    ('T0003', 'Elaine')

SELECT * FROM @Tickets

推荐答案

以下是适用于 SQL Server 2005+ 的解决方案:

Here is a solution that works in SQL Server 2005+:

SELECT t.TicketID,
       STUFF(ISNULL((SELECT ', ' + x.Person
                FROM @Tickets x
               WHERE x.TicketID = t.TicketID
            GROUP BY x.Person
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') [No Preceeding Comma],
       ISNULL((SELECT ', ' + x.Person
                FROM @Tickets x
               WHERE x.TicketID = t.TicketID
            GROUP BY x.Person
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), '') [Preceeding Comma If Not Empty]
  FROM @Tickets t
GROUP BY t.TicketID

参考:

• STUFF (Transact-SQL)

这篇关于我可以用逗号将多行分隔为一列吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！