Transpose data in Oracle database
Transpose data in Oracle database
Requirement:
3 tables in SQL that joined using INNER JOIN with 2 common ID’s. Need to transpose only one of the tables.
TABLE 1:
id_12 | id_number
100 | 1234567
101 | 1234567
400 | 7654321
401 | 7654321
TABLE 2:
id_12 | amountA | amountB | id_23
100 | 1.00 | 5.25 | 888
101 | 3.45 | 9.86 | 889
400 | 2.00 | 9.30 | 950
401 | 6.75 | 3.55 | 951
TABLE 3:
id_23 | amount | amountType|
888 | 1.00 | typeA |
888 | 2.00 | typeB |
888 | 2.25 | typeC |
950 | 1.95 | typeA |
950 | 5.00 | typeB |
950 | 3.75 | typeC |
flip the data from TABLE 3 so that it looks like this:
id_23 | amountA | amountB | type A| typeB | typeC
888 | 1.00 | 5.25 | 1.00 | 2.00 | 2.25
Solution:
create table table2 (
id_12 int,
amountA decimal(10,2),
amountB decimal(10,2),
id_23 int
)
create table table3 (
id_23 int,
amount decimal (10,2),
amountType varchar2(10)
)
insert into table2 values (100 , 1.00 ,5.25 ,888);
insert into table2 values (101, 3.45, 9.86 , 889);
insert into table2 values (400 , 2.00, 9.30 ,950);
insert into table2 values (401, 6.75 , 3.55, 951);
insert into table3 values (888, 1.00 ,'typeA');
insert into table3 values (888, 2.00, 'typeB');
insert into table3 values (888, 2.25,'typeC');
insert into table3 values (950,1.95,'typeA');
insert into table3 values (950,5.00,'typeB' );
insert into table3 values (950, 3.75, 'typeC' );
Result:
REQUEST SOLUTION NOW