SQL11g数据透视表

Question

好吧..。因此，我一整天都在尝试理解枢轴命令，只是无法理解它。

下面的脚本给出了从组件开始日期到3个组件的当前日期(P_CATNR)列出的月份和年份(MON)。

select x.*, nrb.p_catnr, pd.catnr
from 
    (select to_char( add_months( start_date, level-1 ),'Mon-YYYY') Month_from_NR 
    from (select (select min(selection_relse_date) 
                     from BDS_SELECTION_ORG@sid_to_cdsuk st 
                         where substr(selection_no, 2, 99) = '2EDVD0017') start_date, sysdate end_date from dual)
                            connect by level <= months_between(trunc(end_date,'MM'), trunc(start_date,'MM'))+1) x,
            part_description pd 
 INNER JOIN inventory_purchase ip 
         ON ip.part_no = pd.part_no 
 INNER JOIN scm_prodtyp pt 
         ON pt.prodtyp = pd.prodtyp 
 INNER JOIN oes_fod_match acp 
         ON acp.part_no = pd.part_no 
 INNER JOIN NCF_COMPPART ncf 
         ON ncf.item_part_no = pd.part_no 
 INNER JOIN oes_nrbom nrb 
         ON ncf.catnr = nrb.c_catnr 
        AND ncf.prodtyp = nrb.c_prodtyp 
        AND ncf.packtyp = nrb.c_packtyp 
        AND ncf.vernr = nrb.c_vernr 
      WHERE pd.cunr in ('649830', 'W30000') 
        AND pd.catnr = '2EDVD0017' 
        AND pd.fod_idc = 'Y' 
        AND pt.prodgrp = 'AW'

我想要的是按列列出的MONTH_FROM_NR，即

​

​

有人能帮忙吗？

谢谢

Answer 1

with t as (
select x.*, nrb.p_catnr, pd.catnr
from 
    (select to_char( add_months( start_date, level-1 ),'Mon-YYYY') Month_from_NR 
    from (select (select min(selection_relse_date) 
                     from BDS_SELECTION_ORG@sid_to_cdsuk st 
                         where substr(selection_no, 2, 99) = '2EDVD0017') start_date, sysdate end_date from dual)
                            connect by level <= months_between(trunc(end_date,'MM'), trunc(start_date,'MM'))+1) x,
            part_description pd 
 INNER JOIN inventory_purchase ip 
         ON ip.part_no = pd.part_no 
 INNER JOIN scm_prodtyp pt 
         ON pt.prodtyp = pd.prodtyp 
 INNER JOIN oes_fod_match acp 
         ON acp.part_no = pd.part_no 
 INNER JOIN NCF_COMPPART ncf 
         ON ncf.item_part_no = pd.part_no 
 INNER JOIN oes_nrbom nrb 
         ON ncf.catnr = nrb.c_catnr 
        AND ncf.prodtyp = nrb.c_prodtyp 
        AND ncf.packtyp = nrb.c_packtyp 
        AND ncf.vernr = nrb.c_vernr 
      WHERE pd.cunr in ('649830', 'W30000') 
        AND pd.catnr = '2EDVD0017' 
        AND pd.fod_idc = 'Y' 
        AND pt.prodgrp = 'AW'
)
select * from t pivot(
    max(catnr) catnr 
    for month_from_nr 
        in (to_date('10-2007', 'DD-YYYY') as d102007,  to_date('11-2007', 'DD-YYYY') as d112007)
);

你会看到这些列

p_catnr，catnr_d102007，catnr_d112007

我使用max( catnr )是因为我不知道列catnr的性质

您必须手动编写IN子句，或者可以使用PIVOT。在这种情况下，您可以在In子句中使用子查询，但是在这种情况下，查询将返回xml数据。

枢轴XML的一个例子

with t as (
select 'James' name, '2010' dt_year, 100 salary from dual
union all select 'James', '2011', 200 salary from dual
union all select 'James', '2012', 300 salary from dual
union all select 'Lisa', '2012', 450 salary from dual
)
select name, dt_year_xml from t pivot xml (
  sum(salary) sal for dt_year in (select distinct dt_year from t)
);