Friends, I have a pivot question where in I have to convert a row into column based on certain condition. here is the sample data -
id adj_product_type dti_adj
1 BPM DTI_GT45_LTE50
1 BPM DTI_GT45
1 BPS ABC
Expected output
id adj_product_type dti_adj adj_product_type_2 dti_adj_2
1 BPM DTI_GT45_LTE50 BPS ABC
1 BPM DTI_GT45
Requirement: Convert the BPS row as new columns.
Assumptions: There could be more than two rows when adj_product_type=BPM. There will only be one row with adj_product_type=BPS.
Sample data:
create table abc1 (id number,adj_product_type varchar2(100), dti_adj varchar2(100));
insert into abc1 values(1,'BPM','DTI_GT45_LTE50');
insert into abc1 values(1,'BPM','DTI_GT45');
insert into abc1 values(1,'BPS','ABC');
I tried using LEAD but it works only when there is only one row for BPM, any help is appreciated.
select id,adj_product_type,dti_adj,
lead(adj_product_type,1) over (partition by id order by adj_product_type) as adj_product_type_2,
lead(dti_adj,1) over (partition by id order by adj_product_type) as dti_adj_2,
rank() over(partition by id order by adj_product_type) as rnk
from abc1