将多行合并为一行而不进行聚合 [Oracle] [英] Joining multiple rows into a single row without aggregation [Oracle]
本文介绍了将多行合并为一行而不进行聚合 [Oracle]的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下查询:
select type,
date,
amount
from table;
它给出了以下结果:
TYPE DATE AMOUNT
--------------------------------
A 30.6.2019 15
B 30.11.2019 20
C 22.12.2019 17
我想做的是编写一个返回以下内容的查询:
What I want to do is write a query that would return the following:
TYPE1 DATE1 AMOUNT1 TYPE2 DATE2 AMOUNT2 TYPE3 DATE3 AMOUNT3
------------------------------------------------------------------------------------------------------
A 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17
来自第一个查询的行数始终为 3,并且不会超过 3.我无法连接,因为我需要在最终结果集中多列.可以在不使用 PL/SQL 的情况下在 Oracle SQL 中完成吗?
The number of rows from the first query is always going to be 3 and not more than that. I can't concatenate because I need multiple columns in the final result set. Can this be done in Oracle SQL without using PL/SQL?
推荐答案
如果你知道你有三列,枚举它们并聚合:
If you know you have three columns, enumerate them and aggregate:
select max(case when seqnum = 1 then type end) as type1,
max(case when seqnum = 1 then date end) as date1,
max(case when seqnum = 1 then amount end) as amount1,
max(case when seqnum = 2 then type end) as type2,
max(case when seqnum = 2 then date end) as date2,
max(case when seqnum = 2 then amount end) as amount2,
max(case when seqnum = 3 then type end) as type3,
max(case when seqnum = 3 then date end) as date3,
max(case when seqnum = 3 then amount end) as amount3
from (select t.*, rownum as seqnum
from t
) t;
如果不知道返回的列数,则需要使用动态SQL(立即执行
).
If you don't know the number of columns being returned, then you need to use dynamic SQL (execute immediate
).
这篇关于将多行合并为一行而不进行聚合 [Oracle]的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文