我可以用逗号将多行分隔成一列吗？

我正试图将类似的内容合并到 SQLServer 数据库中:

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

变成这样:

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

我需要在 SQLServer 和 Oracle 中执行此操作。

我已经找到了适用于 MySQL 的函数 GROUP_CONCAT，它可以完全满足我的需要，但是 MySQL 在这里不是一个选项。

编辑: 测试台:

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
100018 次浏览
小开

我已经在 Oracle 中找到了这样做的方法，但是在 SQLServer 中仍然需要这样做。

来自 http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings (感谢 https://stackoverflow. com/users/379348/Tang”> Tang )(ORACLE 11及以上)

select
TicketId,
listagg(Person, ', ') People
from
table
group by
TicketId

发信人: 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
小开
最佳答案

下面是一个可以在 SQLServer2005 + 中使用的解决方案:

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

参考文献:

小开

还有 MySQL 版本，为了完整起见:

select
TicketId,
GROUP_CONCAT(Person ORDER BY Person SEPARATOR ', ') People
from
table
group by
TicketId
小开

举个例子

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

或者尝试。

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

/* 这个工程时，我使用这为我的表和信用归功于我的经理，岩石！ */

小开 
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

提交答案