oracle11g - SQL 11g Pivot Tables -

oracle11g - SQL 11g Pivot Tables -

ok ... i've been trying understand pivot commands day , cannot grips it.

the next script gives me months , year (mon-yyyy) listed component start date current date 3 components (p_catnr).

select x.*, nrb.p_catnr, pd.catnr (select to_char( add_months( start_date, level-1 ),'mon-yyyy') month_from_nr (select (select min(selection_relse_date) bds_selection_org@sid_to_cdsuk st substr(selection_no, 2, 99) = '2edvd0017') start_date, sysdate end_date dual) connect level <= months_between(trunc(end_date,'mm'), trunc(start_date,'mm'))+1) x, part_description pd inner bring together inventory_purchase ip on ip.part_no = pd.part_no inner bring together scm_prodtyp pt on pt.prodtyp = pd.prodtyp inner bring together oes_fod_match acp on acp.part_no = pd.part_no inner bring together ncf_comppart ncf on ncf.item_part_no = pd.part_no inner bring together oes_nrbom nrb on ncf.catnr = nrb.c_catnr , ncf.prodtyp = nrb.c_prodtyp , ncf.packtyp = nrb.c_packtyp , ncf.vernr = nrb.c_vernr pd.cunr in ('649830', 'w30000') , pd.catnr = '2edvd0017' , pd.fod_idc = 'y' , pt.prodgrp = 'aw'

what have month_from_nr listed per column, i.e.

can help please?

thanks

with t ( select x.*, nrb.p_catnr, pd.catnr (select to_char( add_months( start_date, level-1 ),'mon-yyyy') month_from_nr (select (select min(selection_relse_date) bds_selection_org@sid_to_cdsuk st substr(selection_no, 2, 99) = '2edvd0017') start_date, sysdate end_date dual) connect level <= months_between(trunc(end_date,'mm'), trunc(start_date,'mm'))+1) x, part_description pd inner bring together inventory_purchase ip on ip.part_no = pd.part_no inner bring together scm_prodtyp pt on pt.prodtyp = pd.prodtyp inner bring together oes_fod_match acp on acp.part_no = pd.part_no inner bring together ncf_comppart ncf on ncf.item_part_no = pd.part_no inner bring together oes_nrbom nrb on ncf.catnr = nrb.c_catnr , ncf.prodtyp = nrb.c_prodtyp , ncf.packtyp = nrb.c_packtyp , ncf.vernr = nrb.c_vernr pd.cunr in ('649830', 'w30000') , pd.catnr = '2edvd0017' , pd.fod_idc = 'y' , pt.prodgrp = 'aw' ) select * t pivot( max(catnr) catnr month_from_nr in (to_date('10-2007', 'dd-yyyy') d102007, to_date('11-2007', 'dd-yyyy') d112007) );

you'll see these columns p_catnr, catnr_d102007, catnr_d112007

i used max(catnr) because don't know nature of column catnr

you have write in clause manually or can utilize pivot xml. in case can utilize subqueries in in clause, query homecoming xml info in case

an illustration of pivot xml

with t ( select 'james' name, '2010' dt_year, 100 salary dual union select 'james', '2011', 200 salary dual union select 'james', '2012', 300 salary dual union select 'lisa', '2012', 450 salary dual ) select name, dt_year_xml t pivot xml ( sum(salary) sal dt_year in (select distinct dt_year t) );

sql oracle11g