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

oracle 列转行1.新建一个名为TEST表 2.向TEST表中添加数据 INSERT INTO TEST(STUDENT,COURSE,SCORE)select '张三','语文',78 from dual unionselect '张三','数学',87 from dual union select '张三','英语',82 from dual unionselect '张三','物理',90 from dual unionselect '李四','语文',65 from dual unionselect '李四','数学',77 from dual unionselect '李四','英语',65 from dual unionselect '李四','物理',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 travelmainid=' ||             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;