oracle pl sql 行转列 ，数据翻转实现

思路：通过存储过程中游标组织sql,返回处理后的sql实现行转列。

oracle 列转行




1.新建一个名为TEST表 

2.向TEST表中添加数据
 
INSERT INTO TEST(STUDENT,COURSE,SCORE)
select '张三','语文',78 from dual union
select '张三','数学',87 from dual union 
select '张三','英语',82 from dual union
select '张三','物理',90 from dual union
select '李四','语文',65 from dual union
select '李四','数学',77 from dual union
select '李四','英语',65 from dual union
select '李四','物理',85 from dual
 
表数据如下：
 


3.列转行
 
方法··1：
 
select 
    Student,
    sum(decode(Course, '数学', Score)) 数学,
    sum(decode(Course, '物理', Score)) 物理,
    sum(decode(Course, '英语', Score)) 英语,
    sum(decode(Course, '语文', Score)) 语文
from 
    TEST 
group by Student
 
方法··2：
 
select
    Student,
    sum(case Course when '数学' then Score else null end) 数学,
    sum(case Course when '物理' then Score else null end) 物理,
    sum(case Course when '英语' then Score else null end) 英语,
    sum(case Course when '语文' then Score else null end) 语文
from 
    TEST 
group by Student

 


注：sum是求和的意思；比如说里面记录里面有两条张三，列转行显示的结果就会是两个张三的结果之和。
(结束）


下面是一个存储过程中 对未知行的转列存储过程。

--根据租户、差旅单ID，获取差旅明细查询SQL（差旅项行转列动态翻转）
  procedure GETTRAVELITEMLIST(p_TenantID     in number,
                              p_TravelMainID in number,
                              p_sql          out varchar) is
    V_SQL varchar(3000);
    CURSOR CURSOR_1 IS
      select traexpensesitemid
        from tm_traexpensesitem
       where enabled = 1
         and tenantid = p_TenantID
       order by traexpensesitemid;
  
  BEGIN
    V_SQL := 'SELECT travelmainid, origin, destination, tmbegindate, tmenddate, cusname, cuscontact';
  
    FOR V_XCLCK IN CURSOR_1 LOOP
      V_SQL := V_SQL || ',' || 'SUM(DECODE(TRAEXPENSESITEMID,''' ||
               V_XCLCK.TRAEXPENSESITEMID || ''',TRAEXPENSES,0)) AS ' || '"' ||
               V_XCLCK.TRAEXPENSESITEMID || '"' || ',' ||
               'SUM(DECODE(TRAEXPENSESITEMID,''' ||
               V_XCLCK.TRAEXPENSESITEMID || ''',traveldetailid,0)) AS ' || '"f' ||
               V_XCLCK.TRAEXPENSESITEMID || '"';
    END LOOP;
  
    V_SQL := V_SQL || ' FROM TM_TRAVELDETAIL WHERE travelmain|
             to_char(p_TravelMainID) ||
             '  GROUP BY travelmainid, origin, destination, tmbegindate, tmenddate, cusname, cuscontact ORDER BY tmbegindate';
    p_sql := V_SQL;
    --DBMS_OUTPUT.PUT_LINE(V_SQL);
    --V_SQL := 'CREATE OR REPLACE VIEW V_TM_TRAVELDETAIL  AS '||  V_SQL;
    --DBMS_OUTPUT.PUT_LINE(V_SQL);
    --EXECUTE IMMEDIATE 'open p_cursor for ' || V_SQL;
  
  END;