ORACLE行专列转换

最新推荐文章于 2024-07-18 16:16:20 发布

jszhangyili

最新推荐文章于 2024-07-18 16:16:20 发布

阅读量1.3k

点赞数

分类专栏： orcale

本文链接：https://blog.youkuaiyun.com/jszhangyili/article/details/8733334

版权

orcale 专栏收录该内容

10 篇文章

订阅专栏

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

student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为 
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group 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;

结果如下图：