Question :
Below is an example of my table:
Column A Column B Column C
Tom Blue 10
Tom Orange 12
Alex Blue 30
Alex Orange 29
Lisa Orange 15
Lisa Blue 14
Output:
Column A Blue Orange
Tom 10 12
Lisa 14 15
The output above returns when there are two duplicate values in Column A, Column B Blue value in Column C is less than Column B Orange value in Column C.
So far I have… select Column A, Column B as "Blue", Column B as "Orange" from table where Blue < Orange;
. Obviously where Blue < Orange doesn’t work but that’s the logic.
I think I need to write a subquery but I cannot figure this out.
Answer :
Create Table
create table t1(
col1 varchar2(20),
col2 varchar2(20),
col3 number
);
Sample Data
insert into t1
select 'Tom','Blue','10' from dual
union all
select 'Tom','Orange', '12' from dual
union all
select 'Alex','Blue','30' from dual
union all
select 'Alex','Orange', '29' from dual
union all
select'Lisa','Orange','15' from dual
union all
select 'Lisa','Blue','14' from dual;
Select Query
select * from
(
select *
from t1
)
pivot
(
MIN(col3) for col2 in ('Blue' as blue,'Orange' as orange)
) where blue<orange;
Output
COL1 BLUE ORANGE
Tom 10 12
Lisa 14 15