将多行合并为一行，Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2690192/
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
Combining multiple rows into one row, Oracle
提问by Torbj?rn
I'm working with a database which is created in Oracle and used in a GIS-software through SDE. One of my colleuges is going to make some statistics out of this database and I'm not capable of finding a reasonable SQL-query for getting the data.
我正在使用一个在 Oracle 中创建并通过 SDE 在 GIS 软件中使用的数据库。我的一位同事将根据该数据库进行一些统计，但我无法找到合理的 SQL 查询来获取数据。
I have two tables, one with registrations and one with registrationdetails. It's a one to many relationship, so the registration can have one or more details connected to it (no maximum number).
我有两张表，一张有注册信息，一张有注册详细信息。这是一种一对多的关系，因此注册可以有一个或多个与之相关的详细信息（没有最大数量）。
- table: Registration
- 表：注册
RegistrationID Date TotLenght 1 01.01.2010 5 2 01.02.2010 15 3 05.02.2009 10
2.table: RegistrationDetail
2.table：注册详情
DetailID RegistrationID Owner Type Distance 1 1 TD UB 1,5 2 1 AB US 2 3 1 TD UQ 4 4 2 AB UQ 13 5 2 AB UR 13,1 6 3 TD US 5
I want the resulting selection to be something like this:
我希望结果选择是这样的：
RegistrationID Date TotLenght DetailID RegistrationID Owner Type Distance DetailID RegistrationID Owner Type Distance DetailID RegistrationID Owner Type Distance 1 01.01.2010 5 1 1 TD UB 1,5 2 1 AB US 2 3 1 TD UQ 4 2 01.02.2010 15 4 2 AB UQ 13 5 2 AB UR 13,1 3 05.02.2009 10 6 3 TD US 5
With a normal join I get one row per each registration and detail. Can anyone help me with this? I don't have administrator-rights for the database, so I can't create any tables or variables. If it's possible, I could copy the tables into Access.
通过正常连接，每个注册和详细信息都会得到一行。谁能帮我这个？我没有数据库的管理员权限，所以我不能创建任何表或变量。如果可能，我可以将表复制到 Access 中。
回答by APC
If the maximum number of Detail records is fixed and known then this can be done. The larger the number the more tedious the query is to code. That's why Nature gave us cut'n'paste.
如果 Detail 记录的最大数量是固定的并且是已知的，则可以这样做。数字越大，查询编码就越乏味。这就是为什么大自然给了我们cut'n'paste。
The following query uses a couple of tricks. The Common Table Expression (aka Sub-Query Factoring) clause encapsulates the query on RegistrationDetail so we can easily refer to it in multiple places. The sub-query uses an Analytic function ROW_NUMBER() which allows us to identify each Detail record within the RegistrationID group. Both these features wwre introduced in Oracle 9i so they aren't new, but lots of people still don't know about them.
以下查询使用了一些技巧。公共表表达式（又名子查询分解）子句封装了对 RegistrationDetail 的查询，因此我们可以轻松地在多个地方引用它。子查询使用分析函数 ROW_NUMBER()，它允许我们识别 RegistrationID 组中的每个详细记录。这两个特性都是在 Oracle 9i 中引入的，所以它们并不新鲜，但很多人仍然不知道它们。
The main query uses Outer Joins to connect the Registration table multiple times to rows in the sub-query. It joins on RegistrationID and the derived DetNo.
主查询使用外部联接将注册表多次连接到子查询中的行。它连接到 RegistrationID 和派生的 DetNo。
SQL> with dets as
2 ( select
3 registrationid
4 , owner
5 , type
6 , distance
7 , detailid
8 , row_number() over (partition by registrationid
9 order by detailid) as detno
10 from registrationdetail )
11 select
12 reg.registrationid
13 , reg.somedate
14 , reg.totlength
15 , det1.detailid as detId1
16 , det1.owner as owner1
17 , det1.type as type1
18 , det1.distance as distance1
19 , det2.detailid as detId2
20 , det2.owner as owner2
21 , det2.type as type2
22 , det2.distance as distance2
23 , det3.detailid as detId3
24 , det3.owner as owner3
25 , det3.type as type3
26 , det3.distance as distance3
27 from registration reg
28 left join dets det1 on ( reg.registrationid = det1.registrationid
29 and det1.detno = 1 )
30 left join dets det2 on ( reg.registrationid = det2.registrationid
31 and det2.detno = 2 )
32 left join dets det3 on ( reg.registrationid = det3.registrationid
33 and det3.detno = 3 )
34 order by reg.registrationid
35 /
REGISTRATIONID SOMEDATE TOTLENGTH DETID1 OW TY DISTANCE1 DETID2 OW TY DISTANCE2 DETID3 OW TY DISTANCE3
-------------- --------- ---------- ---------- -- -- ---------- ---------- -- -- ---------- ---------- -- -- ----------
1 01-JAN-10 5 1 TD UB 1.5 2 AB US 2 3 TD UQ 4
2 01-FEB-10 15 4 AB UQ 13 5 AB UR 13.1
3 05-FEB-09 10 6 TD US 5
SQL>
Obviously if you have four Detail records per RegistrationID you will need four of those Outer Joins (and four sets of columns in the projection).
显然，如果每个 RegistrationID 有四个 Detail 记录，您将需要四个外部联接（以及投影中的四组列）。
edit
编辑
I have just re-read your question and spotted the dread words "No maximum number". Sorry, in that case you're out of luck. The only way of solving this problem with a variable number of sets is with dynamic SQL, which you have effectively ruled out (because you would need to create additional schema objects).
我刚刚重新阅读了您的问题，并发现了“没有最大数量”这个可怕的词。对不起，在那种情况下，你运气不好。使用可变数量的集合解决这个问题的唯一方法是使用动态 SQL，您已经有效地排除了它（因为您需要创建额外的模式对象）。
edit 2
编辑 2
There is another solution, which is just about extracting the data and forgetting the layout. Oracle allows us to declare inline cursors, that is nested selectstatements, in the projection alongside scalars. This passes the problem of displaying the output to a client tool.
还有另一种解决方案，就是提取数据而忘记布局。Oracle 允许我们select在投影中与标量一起声明内联游标，即嵌套语句。这将显示输出的问题传递给客户端工具。
In this version I use Oracle's built-in XML functionality to produce the output (on the basis that lots of tools can render XML these days). The RegistrationDetails records are group within an XMLElement called REG_DETAILS which is nested within each Registration record.
在这个版本中，我使用 Oracle 的内置 XML 功能来生成输出（基于现在许多工具可以呈现 XML）。RegistrationDetails 记录在名为 REG_DETAILS 的 XMLElement 中进行分组，该元素嵌套在每个 Registration 记录中。
with dets as
( select
registrationid
, owner
, type
, distance
, detailid
, row_number() over (partition by registrationid
order by detailid) as detno
from registrationdetail )
select
xmlelement("AllRegistrations"
, xmlagg(
xmlelement("Registration"
, xmlforest( reg.registrationid
, reg.somedate
, reg.totlength
, ( select xmlagg(
xmlelement("RegDetail"
, xmlforest(dets.detailid
, dets.owner
, dets.type
, dets.distance
, dets.detno
)
)
)
from dets
where reg.registrationid = dets.registrationid
) as "RegDetails"
)
)
)
)
from registration reg
order by reg.registrationid
/
回答by Kashif
SELECT
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
wo_h.created_by AS WorkOrderCreator,
wo_h.issue_date As Work_Order_IssueDate,
wo_h.ac_registr As WorkOrderACRegister,
wo_h.state As WorkOrderState,
('Created By:'||wsl.workstep_sign||' On') As Description,
wsl.workstep_date,
listagg('Action Performed By,' ||woa.mutator||'At date' ||
SELECT
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
wo_h.created_by AS WorkOrderCreator,
wo_h.issue_date As Work_Order_IssueDate,
wo_h.ac_registr As WorkOrderACRegister,
wo_h.state As WorkOrderState,
('Created By:'||wsl.workstep_sign||' On') As Description,
wsl.workstep_date,
listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ') WITHIN GROUP ( ORDER BY woa.text)
FROM workstep_link wsl
join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
join wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i
WHERE
wo_h.state = 'O'
AND wo_h.event_perfno_i = '5690136'
AND wo_h.ac_registr = 'AEC'
GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
wsl.workstep_time,
wsl.workstep_sign
woa.action_date|| '.'|| woa.text,'.. ') WITHIN GROUP ( ORDER BY woa.text)
FROM workstep_link wsl
join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
join wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i
WHERE
wo_h.state = 'O'
AND wo_h.event_perfno_i = '5690136'
AND wo_h.ac_registr = 'AEC'
GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
wsl.workstep_time,
wsl.workstep_sign
回答by Kashif
SELECT
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
wo_h.created_by AS WorkOrderCreator,
wo_h.issue_date As Work_Order_IssueDate,
wo_h.ac_registr As WorkOrderACRegister,
wo_h.state As WorkOrderState,
('Created By:'||wsl.workstep_sign||' On') As Description,
wsl.workstep_date,
listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ') WITHIN GROUP ( ORDER BY woa.text)
FROM workstep_link wsl
join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
join wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i
WHERE
wo_h.state = 'O'
AND wo_h.event_perfno_i = '5690136'
AND wo_h.ac_registr = 'AEC'
GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
wsl.workstep_time,
wsl.workstep_sign
回答by thecoop
You can't have multiple columns with the same name in the same query - oracle will rename them as 'Date_1', 'Date_2' etc. What is wrong with having several rows? How are you accessing it?
在同一个查询中不能有多个同名的列——oracle 会将它们重命名为“Date_1”、“Date_2”等。多行有什么问题？你如何访问它？
