Joining multiple rows into a single row without aggregation [Oracle]

Question

I have the following query:

select type,
       date,
       amount
  from table;

And it gives the following result:

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

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?

Are there fixed no of rows or this needs to be done for millions of rows? — Ankit Bajpai, Jan 21 '20 at 14:33
I'm sorry, I should've put that into the post, I'm gonna edit it. It's always going to be 3 rows — jcrudess, Jan 21 '20 at 14:37

score 0 · Answer 1 · answered Jan 21 '20 at 14:54

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;

If you don't know the number of columns being returned, then you need to use dynamic SQL (execute immediate).

Joining multiple rows into a single row without aggregation [Oracle]

1 Answers1

Linked