Oracle之行转列 简栗

当我们查询学生课程及成绩时, 他们会按照列对应排列, 但是我们导出Excel时, 要把课程变成第一行, 该如何做呢?

建表SQL

CREATE TABLE ROOT.STUDENT
(
    NAME VARCHAR2(50),
    CLASS VARCHAR2(50),
    SCORE NUMBER(*)
);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '化学', 80);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '化学', 90);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '语文', 80);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '英语', 89);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '数学', 95);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '数学', 90);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '英语', 85);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '语文', 70);

数据是酱紫的

如何查看我们创建的函数

 

 

-- 查看建了哪些函数，注意，引号内大写
select * from USER_OBJECTS where object_type='PROCEDURE';
select * from user_objects where object_type='VIEW';
-- 查看函数内容，引号内为你要查询的函数名，也要大写
select text from user_source where name='P_DEPT';

-- 创建个简单的函数 小栗子
create or replace function c_hs1(sname varchar2,sclass varchar2) return number is sscore number(7,2);
  begin
    --开始执行
    SELECT score into sscore from STUDENT s WHERE s."NAME" = sname AND s."CLASS" = sclass;
    --返回值
    return sscore;
  end;

select s.NAME,s.CLASS,c_hs1(s.NAME,s.CLASS)score from STUDENT s;

-- 创建函数
create PROCEDURE P_STU IS
  -- 创建一个变量，存放我们的SQL语句
  V_SQL VARCHAR2(4000);
  CURSOR CLASS_CURSOR IS SELECT DISTINCT CLASS FROM student;
  BEGIN
  -- 拼接sql
    V_SQL:= 'SELECT name ';
    FOR C IN CLASS_CURSOR LOOP
      V_SQL:= V_SQL || ',' || ' SUM(DECODE(CLASS,''' || C.CLASS || ''',score,0)) AS ' ||  C.CLASS;
    END LOOP;
    V_SQL := V_SQL || ' FROM student GROUP BY name ';
    V_SQL := ' CREATE OR REPLACE VIEW RESULT  AS ' || V_SQL;
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
 END;

-- 执行函数，生成视图  若存在权限不足 则执行:grant all privileges to 用户名
BEGIN
  P_STU;
END;

----结果
SELECT * FROM  RESULT;

11