EBS_SQL_技巧:关于行转列

遇到一个行转列的问题:

Select h.COMP_NAME,round(h.FINAL_SCORE,2) As FINAL_SCORE,h.CARD_PERIOD_MEANING
from cux_hrsc_headers_t_v h
where 1=1
And CARD_YEAR = P_YEAR
And department_id Is Null 

转换sql:

WITH pivot_data As
     (Select h.COMP_NAME,round(h.FINAL_SCORE,2) As FINAL_SCORE,h.CARD_PERIOD_MEANING
                from cux_hrsc_headers_t_v h
              where 1=1
              And CARD_YEAR = P_YEAR
              And department_id Is Null
              Union All
              Select h.COMP_NAME,round(Avg(h.FINAL_SCORE),2) As FINAL_SCORE,'平均分' As CARD_PERIOD_MEANING
              from cux_hrsc_headers_t_v h
              where 1=1
              And CARD_YEAR = P_YEAR
              And department_id Is Null
              Group By h.COMP_NAME)

Select * from pivot_data
            PIVOT (
            SUM(FINAL_SCORE)        --<-- pivot_clause
            FOR CARD_PERIOD_MEANING          --<-- pivot_for_clause
            IN  ('01月' As "01月",'02月' As "02月",'03月' As "03月",'04月' As "04月",'05月' As "05月",'06月' As "06月",
                 '07月' As "07月",'08月' As "08月",'09月' As "09月",'10月' As "10月",'11月' As "11月",'12月' As "12月",
                 '平均分' As "平均分")   --<-- pivot_in_clause
            )
            Order By 14 Desc

之后发现需要用视图,并且要带入参数:于是

create or replace package p_view_param is

  -- Author  : MARTIN
  -- Created : 2016/10/28 10:13:56
  -- Purpose : 
  
 Function set_param(num Varchar2) Return Varchar2; 
 Function get_param Return Varchar2; 

end p_view_param;
/
create or replace package body p_view_param is
 paramValue Varchar2(50); 
 Function set_param(num Varchar2) Return Varchar2 Is 
 Begin 
 paramValue:=num; 
 Return num;
 end;
 Function get_param Return Varchar2 Is
 Begin 
 Return paramValue; 
 end; 
end p_view_param;

创建视图:

create or replace view cux_hrsc_org_rank_v as 
Select * from (Select h.COMP_NAME,round(h.FINAL_SCORE,2) As FINAL_SCORE,h.CARD_PERIOD_MEANING
                from cux_hrsc_headers_t_v h
              where 1=1
              And CARD_YEAR = p_view_param.get_param()
              And department_id Is Null
              Union All
              Select h.COMP_NAME,round(Avg(h.FINAL_SCORE),2) As FINAL_SCORE,'平均分' As CARD_PERIOD_MEANING
              from cux_hrsc_headers_t_v h
              where 1=1
              And CARD_YEAR = p_view_param.get_param()
              And department_id Is Null
              Group By h.COMP_NAME)
PIVOT (
SUM(FINAL_SCORE)        --<-- pivot_clause
FOR CARD_PERIOD_MEANING          --<-- pivot_for_clause
IN  ('01月','02月','03月','04月','05月','06月','07月','08月','09月','10月','11月','12月','平均分')   --<-- pivot_in_clause
)
Order By 14 desc
;

sql中引用:

select * from cux_hrsc_org_rank_v where p_view_param.set_param(2016)=2016

 然后发现oracle将'01月'作为了标题列,那么要如何引用呢?

参照:

显然，Oracle将其识别为了字符串。应该怎么做呢？我们需要使用双引号。之所以Oracle会识别错误，就因为’’的常量识别优先级高于列名称优先级。我们需要让Oracle忽略这个特点，就使用双引号。

http://blog.itpub.net/17203031/viewspace-754807/

create or replace view cux_hrsc_DEPT_rank_v as 
WITH pivot_data As
     (Select h.DEP_NAME As COMP_NAME,round(h.FINAL_SCORE,2) As FINAL_SCORE,h.CARD_PERIOD_MEANING
                from cux_hrsc_headers_t_v h
              where 1=1
              And CARD_YEAR = p_view_param.get_param()
              And ORGANIZATION_ID = 81
              And department_id Is Not Null
              Union All
              Select h.DEP_NAME As COMP_NAME,round(Avg(h.FINAL_SCORE),2) As FINAL_SCORE,'平均分' As CARD_PERIOD_MEANING
              from cux_hrsc_headers_t_v h
              where 1=1
              And CARD_YEAR = p_view_param.get_param()
              And ORGANIZATION_ID = 81
              And department_id Is Not Null
              Group By h.DEP_NAME)

Select COMP_NAME,"'01月'" M01,"'02月'" M02,"'03月'" M03,"'04月'" M04,"'05月'" M05,"'06月'" M06,
                 "'07月'" M07,"'08月'" M08,"'09月'" M09,"'10月'" M10,"'11月'" M11,"'12月'" M12,
                 "'平均分'"  平均分
from pivot_data
            PIVOT (
            SUM(FINAL_SCORE)        --<-- pivot_clause
            FOR CARD_PERIOD_MEANING          --<-- pivot_for_clause
            IN  ('01月','02月','03月','04月','05月','06月',
                 '07月','08月','09月','10月','11月','12月',
                 '平均分')   --<-- pivot_in_clause
            )
Order By 14 Desc
            
            
 
select * from cux_hrsc_org_rank_v where p_view_param.set_param(2016)=2016

Cursor Cur_Template Is
 select * from cux_hrsc_org_rank_v h where p_view_param.set_param(P_YEAR)=P_YEAR;

FOR r_lookups IN Cur_Template Loop
      cux_conc_utl.out_msg('<LINE>');
      cux_conc_utl.out_msg('<COMP_NAME>' ||r_lookups.COMP_NAME||'</COMP_NAME>');
      cux_conc_utl.out_msg('<M01>' ||r_lookups.M01||'</M01>');