SQL 我可以用逗号将多行分隔为一列吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2046037/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I Comma Delimit Multiple Rows Into One Column?
提问by John Gietzen
I am attempting to merge something like this in my SQL Server database:
我试图在我的 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.
我需要在 SQL Server 和 Oracle 中执行此操作。
I have found the function GROUP_CONCATfor MySQL that does exactly what I need here, but MySQL is not an option here.
我找到了GROUP_CONCATMySQL的函数，它完全满足我的需要，但 MySQL 不是这里的一个选项。
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
回答by OMG Ponies
Here is a solution that works in SQL Server 2005+:
这是一个适用于 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
Reference:
参考：
回答by John Gietzen
And, the MySQL version, for completeness:
而且，MySQL 版本，为了完整性：
select
TicketId,
GROUP_CONCAT(Person ORDER BY Person SEPARATOR ', ') People
from
table
group by
TicketId
回答by satish
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
Select [TicketID],
STUFF((SELECT ',' + Person FROM @Tickets WHERE (
TicketID=Result.TicketID) FOR XML PATH ('')),1,1,'') AS BATCHNOLIST
From @Tickets AS Result
GROUP BY TicketID
回答by John Gietzen
I have found a way to do this in Oracle, but I still need to do it in SQL Server.
我已经找到了在 Oracle 中执行此操作的方法，但我仍然需要在 SQL Server 中执行此操作。
From http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings(Thanks tanging)(ORACLE 11 and up)
来自http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings （感谢tanging）（ORACLE 11 及更高版本）
select
TicketId,
listagg(Person, ', ') People
from
table
group by
TicketId
From: http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html
来自：http: //halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html
with
data
as
(
select
TicketId,
Person,
ROW_NUMBER() over (partition by TicketId order by Person) "rownum",
COUNT(*) over (partition by TicketId) "count"
from
Table
)
select
TicketId,
LTRIM(sys_connect_by_path(Person,','),',') People
from
data
where
"rownum" = "count"
start with
"rownum" = 1
connect by
prior TicketId = TicketId
and
prior "rownum" = "rownum" - 1
order by
TicketId
回答by Nishad M
one example
一个例子
SELECT DISTINCT
t.TicketID,
STUFF((SELECT ', ', i.Person as [text()]
FROM @Tickets i
WHERE i.TicketID = t.TicketID
FOR XML PATH ('')), 1, 2, '') as People
FROM
@Tickets t
......... or try ..............
……或者试试…………
SELECT DISTINCT
t.TicketID,
STUFF((SELECT ', ' + i.Person /* notice this line is different */
FROM @Tickets i
WHERE i.TicketID = t.TicketID
FOR XML PATH ('')), 1, 2, '') as People
FROM
@Tickets t
/* this works when I used this for my table and credit goes to my manager that ROCKS! */
/* 当我把它用在我的桌子上时，这是有效的，而且归功于我的经理，ROCKS！*/
