列转行的一视图

-------------------------------------------------------------------

--供大家参考

-- 列转行的一视图

-------------------------------------------------------------------

CREATE OR REPLACE VIEW RM.V_HRM_RSTJ_YEARD_GRAPH AS
select k.cname,k.now_gz,
k.year_month,
k.year_d
from
(
SELECT a.cname,
       sum(a.renshu) now_gz,
       a.year_month,
       a.year_d
  FROM rm.hrm_rstj a
 --WHERE a.year_d = '2008' --to_char(:ls_end, 'yyyy')
 GROUP BY a.cname,a.year_month,a.year_d

union all
/*zm 081010*/
select '合计' cname,
       sum(b.renshu) now_gz,
       b.year_month,
       b.year_d
from rm.hrm_rstj b
--where b.year_d = '2008'
group by b.year_month,b.year_d

union all

SELECT '计划人数' canme,
       sum(decode(t.rn,
                  1,
                  t.month1,
                  2,
                  t.month2,
                  3,
                  t.month3,
                  4,
                  t.month4,
                  5,
                  t.month5,
                  6,
                  t.month6,
                  7,
                  t.month7,
                  8,
                  t.month8,
                  9,
                  t.month9,
                  10,
                  t.month10,
                  11,
                  t.month11,
                  12,
                  t.month12)) now_gz,
       to_char(t.rn) year_month,
       t.year_d
  FROM (SELECT m.*, n.rn
          FROM RM.HRM_EMP_JH m,
               (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 12) n) t
 --where t.year_d = '2008'
 group by t.rn,t.year_d
) k

-------------------------------------------------------------------

--供大家参考

--下面为表结构

-------------------------------------------------------------------

create or replace procedure rm.pro_hrm_rstj(ls_year varchar2) is
  /*declare
  ls_year varchar(30) default '2008';*/
  i integer;
begin
  /*zm--20081009*/
  if ls_year <= to_char(sysdate, 'yyyy') then
    delete from rm.hrm_rstj where year_d = ls_year;
 
    for i in 1 .. 12 loop
      if ls_year = to_char(sysdate, 'yyyy') and
         i > to_number(to_char(sysdate, 'mm')) then
        dbms_output.put_line(ls_year || '年' || i || '月人数不进行统计');
      else
        insert into rm.hrm_rstj
          (select ls_year,
                  i,
                  count(nvl(n.renshu, 0)) renshu,
                  nvl(m.cname, '无分类') cname
             from (SELECT CANP.COMMDICT.CNAME, CANP.COMMDICT.CCODE
                     FROM CANP.COMMDICT
                    WHERE CANP.COMMDICT.CTYPE = 'archives_role') m,
                  (SELECT a.renshu, nvl(a.file_degree, '无') gw
                     FROM rm.v_hrm_emp_info a
                    where (lea_sce_date is null or
                          to_char(lea_sce_date, 'yyyy-mm') >
                          ls_year || '-' || i)
                      and to_char(ente_appe_date, 'yyyy-mm') <=
                          ls_year || '-' || i) n
            where m.ccode(+) = n.gw
            group by m.cname);
        commit;
      end if;
    end loop;
  else
    dbms_output.put_line('统计年度大于当前年度');
  end if;
end pro_hrm_rstj;

-----------------------------------------------------------------

 

--RM.HRM_RSTJ为一通过上面procedure统计插入的
-----------------------

create table RM.HRM_RSTJ
(
  YEAR_D     VARCHAR2(30),
  YEAR_MONTH VARCHAR2(30),
  RENSHU     NUMBER(8,1),
  CNAME      VARCHAR2(30)
)

 

create table RM.HRM_EMP_JH
(
  JH_ID       NUMBER(38) not null,
  YEAR_D      VARCHAR2(30),
  FILE_DEGREE VARCHAR2(30),
  MONTH1      NUMBER(8),
  MONTH2      NUMBER(8),
  MONTH3      NUMBER(8),
  MONTH4      NUMBER(8),
  MONTH5      NUMBER(8),
  MONTH6      NUMBER(8),
  MONTH7      NUMBER(8),
  MONTH8      NUMBER(8),
  MONTH9      NUMBER(8),
  MONTH10     NUMBER(8),
  MONTH11     NUMBER(8),
  MONTH12     NUMBER(8),
  BZ_MAN      VARCHAR2(30),
  BZ_TIME     DATE,
  REMARK      VARCHAR2(400),
  PARM1       VARCHAR2(80),
  PARM2       VARCHAR2(80)
)