Hello
Need help in pivoting this data set, the Pivot takes MIN/MAX on a column and creates only row, my goal is to preserve all rows. We need to pivot on DSTRCT_CODE which will have only two values {BBBB/CCCC} on value of column PREF_PART_IND.so for below sample data:
A Supplier parts will have PREF_PART_IND flag, which we use to ORDER the part, prefrence is alway ‘01’ and so on. There are Two Distrct_code in our system, and for the same part these district may have different preference, So we don't want to loose those.
with q_data as
(
select 'BBBB' DSTRCT_CODE,'001201624777' STOCK_CODE,'KENNMTL' MNEMONIC,'1010784' PART_NO,'01' PREF_PART_IND from dual union all
select 'BBBB','001201624777','KENNMTL ','1010784','02' from dual union all
select 'BBBB','001201624777','KENNMTL ','1010784','98' from dual union all
select 'CCCC','001201624777','KENNMTL ','1010784','98' from dual union all
select 'BBBB','001201624777','ATLAS','9753237605','02' from dual union all
select 'CCCC','001201624777','ATLAS','9753237605','98' from dual union all
select 'BBBB','001201624777','EPIROC','9753250016','01' from dual union all
select 'BBBB','001201624777','EPIROC','9753250016','02' from dual union all
select 'BBBB','001201624777','KENNMTL','C-1HDSR','01' from dual union all
select 'BBBB','001201624777','KENNMTL','C-1HDSR','02' from dual union all
select 'BBBB','001201624777','KENNMTL','C-1HDSR','03' from dual union all
select 'BBBB','001201624777','KENNMTL','C-1HDSR','98' from dual union all
select 'CCCC','001201624777','KENNMTL','C-1HDSR','01' from dual union all
select 'BBBB','001201624777','KENNMTL','5TG3','01' from dual UNION ALL
select 'CCCC','001201624777','EPIROC','5TG3','01' from dual UNION ALL
select 'CCCC','001201624777','WEE','FTG','01' from dual UNION ALL
select 'CCCC','001201624777','WEE','FTG','02' from dual UNION ALL
select 'BBBB','001201624777','SONY','HTYR5','98' from dual UNION ALL
select 'BBBB','001201624777','SONY','HTYR5','99' from dual
),
p_data as
( SELECT distinct DSTRCT_CODE, STOCK_CODE, MNEMONIC, PART_NO, PREF_PART_IND
FROM q_data
where DSTRCT_CODE in ('BBBB','CCCC')
)
select * from (
select DSTRCT_CODE, STOCK_CODE, MNEMONIC, PART_NO, PREF_PART_IND from p_data
)
pivot (
MIN(PREF_PART_IND) for DSTRCT_CODE in (
'BBBB' BBBB, 'CCCC' CCCC
)
)
order by 2,3;
The Output I am expecting is
So don't want to loose any value for PREF_PART_IND