0

I have two tables they are more complex so for simplicity I created simpler versions but I get similar output as you can see in my sql join. each supplier has as many rows as many surcharge codes the item has.thr item can have no surcharge or 1-n. for simplicity I created just 3 codes byt in the surcharge table there are more than 50 codes and I dont know how many of them for an item will be in the output table, I see it after join when I see repeated rows for supplier with the same item and turnover.

create table supplier (
supplier_ID number,
supplier_name varchar2(20),
item varchar2(12),
turnover number,
surcharge_id number
);

create table surcharge (
surcharge_ID number,
surcharge_code number,
basic_value number,
surcharge_name varchar2(12),
weight number
);

insert into supplier values ( 1 , 'ABC_company' , 'A' , 1000, 1 );
insert into supplier values ( 1 , 'ABC_company' , 'A' , 1000, 2 );
insert into supplier values ( 1 , 'XYZ_company' , 'X' , 50, 1 );
insert into supplier values ( 1 , 'XYZ_company' , 'X' , 50, 2 );
insert into supplier values ( 1 , 'XYZ_company' , 'X' , 50, 3 );
insert into supplier values ( 1 , 'KLM_company' , 'K' , 280, null );

insert into surcharge values ( 1 , 20 , 0.5 , 'gold', 0.112 );
insert into surcharge values ( 2 , 50 , 0.3 , 'silver', 0.080 );
insert into surcharge values ( 3 , 80 , 0.1 , 'bronze', 0.010 );


select supplier_name,item,turnover,surcharge_code,basic_value,surcharge_name,weight 
from supplier 
left join surcharge on supplier.surcharge_id=surcharge.surcharge_ID
order by supplier_name,item;

SUPPLIER_NAME   ITEM    TURNOVER    SURCHARGE_CODE  BASIC_VALUE SURCHARGE_NAME     WEIGHT
ABC_company       A       1000         20                 .5            gold        .112
ABC_company       A       1000         50                 .3            silver      .08
KLM_company       K       280          -                  -              -           - 
XYZ_company       X       50           50                 .3            silver      .08
XYZ_company       X       50           80                 .1            bronze      .01
XYZ_company       X       50           20                 .5            gold        .112

it is possible to pivot such table to this desired output?bear in mind, you dont know what code and how many of them if any will be joined.

desired output

SUPPLIER_NAME   ITEM    TURNOVER       20      50     80    gold        silver     bronze 
 ABC_company      A       1000         .5      .3     -     .112        .08        -
 KLM_company      K       280          -       -      -     -           -          -
 XYZ_company      X       50           .5      .3     .1    .112        .08        .01
Pato
  • 153
  • 6
  • [Why is “Is it possible to…” a poorly worded question?](https://softwareengineering.meta.stackexchange.com/q/7273/202153) – Andreas Nov 26 '20 at 22:38
  • If you want to pivot the rows then you need to know what values there will be beforehand. If you want a dynamic pivot then you are better doing it in whatever client programming language you are using to access the database. – MT0 Nov 26 '20 at 22:52
  • 1
    [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b96cdc1a04178fa24cf4bb162d932349) is a non-dynamic pivot of your data. – MT0 Nov 26 '20 at 23:04

0 Answers0