Hello,
Is it possible to write a single SQL query that would concatenate multiple rows into a single row with multiple columns?
For example, I have a table with the following data:
TYPE DATE AMOUNT
--------------------------------
B 30.6.2019 15
B 30.11.2019 20
C 22.12.2019 17
And I want a query that would return a single row:
TYPE1 DATE1 AMOUNT1 TYPE2 DATE2 AMOUNT2 TYPE3 DATE3 AMOUNT3
-------------------------------------------------------------------------
B 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17
The column names don't really matter, I just need to combine the original three rows into a single row with multiple columns, so I can't use concatenation.
I know that there will always be 3 rows in the original table, so there's not going to be a million columns in the final result.
For performance reasons, I would like to avoid using PL/SQL if at all possible, and just do it in a single query, but I'm not sure if it's possible.
Thank you!
Sounds like you need to PIVOT!
Assign each row a unique number (e.g. with row_number()) first, then pivot the values:
with rws as (
select 'B' type, to_date ( '30.6.2019 ', 'dd.mm.yyyy' ) dt, 15 num from dual union all
select 'B' type, to_date ( '30.11.2019', 'dd.mm.yyyy' ) dt, 20 num from dual union all
select 'C' type, to_date ( '22.12.2019', 'dd.mm.yyyy' ) dt, 17 num from dual
), rns as (
select row_number () over ( order by type ) rn,
r.*
from rws r
)
select * from rns
pivot (
min ( type ) tp, min ( dt ) dt, min ( num )
for rn in ( 1, 2, 3 )
);
1_TP 1_DT 1 2_TP 2_DT 2 3_TP 3_DT 3
B 30-JUN-2019 00:00:00 15 B 30-NOV-2019 00:00:00 20 C 22-DEC-2019 00:00:00 17 Read more at:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot