我正在尝试在我的SQL Server数据库中合并这样的内容:
[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中都执行此操作。
我已经找到了适用于MySQL的
GROUP_CONCAT
函数,它完全满足我在这里的需要,但是MySQL在这里不是一个选择。 br /> 编辑:测试台:
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
#1 楼
这是在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)
#2 楼
并且,为了完整起见,MySQL版本:select
TicketId,
GROUP_CONCAT(Person ORDER BY Person SEPARATOR ', ') People
from
table
group by
TicketId
#3 楼
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
评论
在我的场景中,“ Person”属于Money类型,并且该解决方案被证明比“ OMG Ponies”解决方案更快,更准确。
–奥马尔
2014年1月21日在22:16
确认它运作良好-一百万泰铢!
–瓦格纳·达达·达席尔瓦·菲略(Wagner Danda da Silva Filho)
2014年1月29日15:26
#4 楼
我已经找到了在Oracle中执行此操作的方法,但是我仍然需要在SQL Server中执行此操作。从http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2 -listagg-new-aggregation-operator用于创建逗号分隔的字符串(感谢缠结)(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
评论
+1:Oracle示例的荣誉。在Oracle中有两种方法可以做到这一点,但有些方法包括使用不受支持的功能。
–OMG小马
2010年1月12日,下午1:33
我知道这很旧,但是如果您使用的是11g,则有ListAgg(它看起来与MySQL Group_CONCAT非常相似):technology.amis.nl/blog/6118/…&download.oracle.com/docs/cd/ E11882_01 / server.112 / e10592 / ...
–哈里森
2010年7月7日在19:13
@tanging:太棒了!我正在编辑我的回复以反映这一点!
–John Gietzen
2010年7月7日在22:47
指向LISTAGG文档docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm的正确链接
–凯
18年7月13日在15:50
#5 楼
一个示例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
/ *
当我将其用于餐桌时,此功能有效,功劳归我的经理ROCKS!
* /
评论
这留下了逗号结尾。
–John Gietzen
13年3月14日在19:58
我以使此匹配为例。这比以前的最佳答案的执行速度快得多。
–John Gietzen
13年3月14日在20:11
@JohnGietzen性能提升并非没有代价。对于包含&的值,这将无法正常工作。
– Mikael Eriksson
2013年3月14日20:41
使用Disntinct也不是最好的方法,如果使用大量数据进行测试,则会发现按解决方案分组的速度更快。
– Mikael Eriksson
13年3月14日在20:46
看一下有关这个答案的评论。 stackoverflow.com/questions/9811577 / ...
– Mikael Eriksson
13年3月14日在20:55
评论
这将无法正常工作。...您进行的分组操作没有使用people字段,因此失败了,并且xml路径无法将逗号(,)作为标识符,因为它无法从中创建元素。
–加布里埃莱·彼得里奥利(Gabriele Petrioli)
2010年1月12日在2:04
坏蛋!您在底部缺少分组依据,但是很棒!
–John Gietzen
2010年1月12日,2:10
我很好奇:使用服务器上的查询运行或将结果串联到内存中,运行速度更快吗?
–llamaoo7
2010年1月12日,下午3:10
为此,游标将是必要的,并且据我所知,它们非常慢。
–John Gietzen
2010年1月12日14:11
出于其价值,我过去有类似的需求,并尝试了许多不同的方法,包括编写自己的CLR .NET聚合函数。到目前为止,OMG Ponies展示的XPATH解决方案是最快的。
–理查德
11年1月28日在23:18