oracle横纵表相互转换

/*纵表转横表*/   

CREATE GLOBAL TEMPORARY TABLE test2
(
      uname VARCHAR2(10),
      subject VARCHAR2(10),
      score NUMBER
);


INSERT INTO test2 VALUES('王五','语文',80);
INSERT INTO test2 VALUES('李四','语文',90);
INSERT INTO test2 VALUES('李四','数学',80);
INSERT INTO test2 VALUES('李四','英语',70);
INSERT INTO test2 VALUES('张三','语文',80);
INSERT INTO test2 VALUES('张三','数学',80);
INSERT INTO test2 VALUES('张三','英语',90);

SELECT * FROM test2;

SELECT
        t.uname AS 姓名, 
        SUM(CASE t.subject WHEN '语文' THEN t.score END)AS 语文,
        SUM(CASE t.subject WHEN '数学' THEN t.score END)AS 数学,
        SUM(CASE t.subject WHEN '英语' THEN t.score END)AS 英语      
FROM test2 t
     GROUP BY t.uname;

/*横表转纵表 */ 

CREATE GLOBAL TEMPORARY TABLE test4
(
      uname VARCHAR2(10),
      chinese NUMBER,
      math NUMBER,
      english NUMBER
);

INSERT INTO test4 VALUES('张三',80,90,70);
INSERT INTO test4 VALUES('李四',90,85,95);
INSERT INTO test4 VALUES('王五',88,75,90);

SELECT * FROM test4;

SELECT * FROM (
       SELECT  t.uname,'chinese' AS subject,t.chinese AS score FROM test4 t
       UNION ALL
       SELECT  t.uname,'math' AS subject,t.math AS score FROM test4 t
       UNION ALL
       SELECT  t.uname,'english' AS subject,t.english AS score FROM test4 t
       UNION ALL
       SELECT t.uname,'平均分' AS subject,CAST((t.chinese+t.math+t.english)*1.0/3 AS DECIMAL(18,2)) AS score FROM test4 t
        UNION ALL
       SELECT t.uname,'总分' AS subject,(t.chinese+t.math+t.english) AS score FROM test4 t
)tb
ORDER BY uname,
case tb.subject
   when 'chinese' then 1 
   when 'math' then 2 
   when 'english' then 3 
   when '平均分' then 4
   when '总分' then 5
END   

添加上计算总分和平均分

SQL语句最后加上case相当于排序。