将多行合并为一行而不进行聚合 [Oracle] [英] Joining multiple rows into a single row without aggregation [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屋！