I have this structure in table
TYPE DATE AMOUNT ID -------------------------------- B 30.6.2019 15 1 B 30.11.2019 20 1 C 22.12.2019 17 1 B 30.6.2019 15 2 B 30.11.2019 20 2 C 22.12.2019 17 2
and i need it in flat one row multiple columns structure
TYPE1 DATE1 AMOUNT1 TYPE2 DATE2 AMOUNT2 TYPE3 DATE3 AMOUNT3 ID ----------------------------------------------------------------------------------- B 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17 1 B 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17 2
Please help with the query.
Advertisement
Answer
In Oracle, You can use the PIVOT as follows:
SQL> -- Sample data
SQL> WITH YOUR_TABLE(TYPE,"DATE",AMOUNT,ID) AS
2 (
3 SELECT 'B', TO_DATE('30.06.2019','DD.MM.RRRR'), 15, 1 FROM DUAL UNION ALL
4 SELECT 'B', TO_DATE('30.11.2019','DD.MM.RRRR'), 20, 1 FROM DUAL UNION ALL
5 SELECT 'C', TO_DATE('22.12.2019','DD.MM.RRRR'), 17, 1 FROM DUAL UNION ALL
6 SELECT 'B', TO_DATE('30.06.2019','DD.MM.RRRR'), 15, 2 FROM DUAL UNION ALL
7 SELECT 'B', TO_DATE('30.11.2019','DD.MM.RRRR'), 20, 2 FROM DUAL UNION ALL
8 SELECT 'C', TO_DATE('22.12.2019','DD.MM.RRRR'), 17, 2 FROM DUAL )
9 -- Your query starts from here
10 SELECT * FROM (
11 SELECT T.*,
12 ROW_NUMBER() OVER(PARTITION BY ID ORDER BY "DATE") AS RN
13 FROM YOUR_TABLE T
14 ) PIVOT (
15 MAX ( TYPE ) AS TYPE, MAX ( "DATE" ) AS "DATE", MAX ( AMOUNT ) AS AMOUNT
16 FOR RN IN ( 1, 2, 3 )
17 );
ID 1 1_DATE 1_AMOUNT 2 2_DATE 2_AMOUNT 3 3_DATE 3_AMOUNT
---------- - --------- ---------- - --------- ---------- - --------- ----------
1 B 30-JUN-19 15 B 30-NOV-19 20 C 22-DEC-19 17
2 B 30-JUN-19 15 B 30-NOV-19 20 C 22-DEC-19 17
SQL>