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数据库中合并这样的内容:
引用:
STUFF (Transact-SQL)
(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
引用:
关于sql - 我可以用逗号分隔多行成一列吗? ,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2046037/