Oracle create a Pivot table from 3 columns

I’m trying to create an excel-like pivot table with 3 columns of information, order tpye, product code, and the income generated from that order.

I’ve managed to get the generated income for every order from the same product, ordered by the order tpye.

select ORDER_TYPE, PRODUCT_CODE, sum(INCOME)
from Logistics
group by ORDER_TYPE, PRODUCT_CODE
order by ORDER_TYPE;

A fragment from the output query:

ORDER_TYPE, PRODUCT_CODE, sum(INCOME):
EB  ZOL 432919
EB  ITA 24832674
EB  ELM 2095035
EB  FRI 1464608
EB  ESZ 0
EB  GON 1707758
EB  PVE 23130
EK  FRI 10560880
EK  ITA 30207062
EK  PVE 1625576
EK  ESZ 0
EK  ZOL 1467432
EK  GON 11208618
EK  ELM 14159542
ER  PVE -12449
ER  ITA -3808222
ER  ELM -236587
ER  ZOL -17394
ER  GON -16758710
ER  FRI -102844
ER  ESZ -104169
ER      33142
ES  ZOL 13883
ES  FRI -12860
ES  ELM -107442
ES  SZO -46800
ES  PVE 0
ES  GON 0
ES  ITA -61427
E1  ELM 29195518

As you can see I have now a couple of rows for every order type because of the differenct products.

How can I modify this query to get a pivot table which has the columns for every order type and the rows for the product codes, so I only have one column for the order types instead of rows?

For example:

    EB    EK    ES    ER
ZOL income datas
ITA for every
ELM cell
FRI 
ESZ 
GON 
PVE 
FRI 

Answer

You tagged Oracle18c so this should work for your version. I tested this on 11g.

SELECT *
FROM (
  SELECT product_code, order_type, income
  FROM Logistics
)
PIVOT (
  sum(income)  
  for order_type
  IN ('EB' AS EB, 'ER' AS ER, 'ES' AS ES, 'EK' AS EK)
);

This does require the set for the IN list to be filled in prior to execution. There is another syntax that allows a sub select but it returns XML. If you try to replace PIVOT XML with PIVOT it gives an error.

WITH orderTypes AS
(
    select 'EB' as order_type from dual union all
    select 'ER' as order_type from dual union all
    select 'ES' as order_type from dual union all
    select 'EK' as order_type from dual union all 
    select 'AA' as order_type from dual union all
    select 'AB' as order_type from dual union all
    select 'AC' as order_type from dual union all
    select 'AD' as order_type from dual union all
    select 'AE' as order_type from dual        

)
SELECT *
FROM (
  SELECT l.product_code, l.order_type, l.income
  FROM Logistics l
)
PIVOT XML  (
  sum(income) AS orderSum
  for order_type 
  IN ( select order_type from orderTypes)
);