ORACLE行专列转换

来源：互联网发布：京东模拟登陆php代码编辑：程序博客网时间：2024/04/27 14:14

一、采用SQL decode和PL/SQL函数实现
1、固定列数的行列转换
如

student subject grade---------------------------student1 语文 80student1 数学 70student1 英语 60student2 语文 90student2 数学 80student2 英语 100……转换为 语文 数学 英语student1 80 70 60student2 90 80 100……语句如下：select student,sum(decode(subject,'语文', grade,null)) "语文",sum(decode(subject,'数学', grade,null)) "数学",sum(decode(subject,'英语', grade,null)) "英语"from tablegroup by student

2、动态的行列转换需要通过存储过程拼接sql语句

如：

CREATE OR REPLACE PROCEDURE GET_INSPECT_ITEM(    P_INSPECT_ID        IN VARCHAR2    ,Re_CURSOR           OUT SYS_REFCURSOR )AS  SQLSTR VARCHAR2 (4000);BEGIN   DECLARE   CURSOR C_CURSOR   IS   SELECT DISTINCT QCITEM_NAME     FROM REP_INSPECT_ITEM      WHERE INSPECT_ID=P_INSPECT_ID;    c_row C_CURSOR %ROWTYPE;    BEGIN             SQLSTR:='SELECT INSPECT_ID,SAMPLE_ID';              FOR c_row IN C_CURSOR LOOP              SQLSTR:=SQLSTR||', MAX(DECODE(T.QCITEM_NAME,''' ||c_row.QCITEM_NAME||''',T.VALUE,null)) AS "' || c_row.QCITEM_NAME || '"';              END LOOP;              SQLSTR:=SQLSTR||'FROM REP_INSPECT_ITEM T GROUP BY SAMPLE_ID,INSPECT_ID HAVING INSPECT_ID='||P_INSPECT_ID;              OPEN Re_CURSOR FOR SQLSTR ;   END;END GET_INSPECT_ITEM;

结果如下图：