This question already has answers here:
How to concatenate text from multiple rows into a single text string in SQL server?

(48个答案)


5年前关闭。




我正在尝试在SQL Server数据库中合并这样的内容:
[TicketID], [Person]
 T0001       Alice
 T0001       Bob
 T0002       Catherine
 T0002       Doug
 T0003       Elaine

Into this:

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

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

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

EDIT: Test bench:

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+中可以使用的解决方案:

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)
  • 关于sql - 我可以用逗号分隔多行成一列吗? ,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2046037/

    10-09 05:34
    查看更多