Oracle行列转换的方式

本文主要是介绍Oracle行列转换的方式，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、创建score_表，并插入数据

CREATE TABLE SCORE_1
(SNO VARCHAR2(10) NOT NULL,
CNAME VARCHAR2(20) NOT NULL,
SCORE NUMBER NOT NULL);

INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (103,'CHINESE',86);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (105,'CHINESE',75);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (109,'CHINESE',68);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (103,'MATH',92);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (105,'MATH',88);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (109,'MATH',76);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (103,'ENGLISH',64);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (105,'ENGLISH',91);
INSERT INTO SCORE_1(SNO,CNAME,SCORE)VALUES (109,'ENGLISH',78);

 二、创建score_2表，并插入数据

create table
(
  sno VARCHAR2(10) not null,
  语文  VARCHAR2(20) not null,
  数学  VARCHAR2(20) not null,
  英语  VARCHAR2(20) not null
);

INSERT INTO SCORE_58 VALUES (103,86,92,64);
INSERT INTO SCORE_58 VALUES (105,75,88,91);
INSERT INTO SCORE_58 VALUES (109,68,76,78);

三、score_1==>score_2

1.case when

SELECT SNO,
       MAX(CASE
             WHEN CNAME = 'CHINESE' THEN
              SCORE
             ELSE
              0
           END) 语文,
       MAX(CASE
             WHEN CNAME = 'MATH' THEN
              SCORE
             ELSE
              0
           END) 数学,
       MAX(CASE
             WHEN CNAME = 'ENGLISH' THEN
              SCORE
             ELSE
              0
           END) 英语
  FROM SCORE_1
 GROUP BY SNO;

 2.pivot

语法：SELECT * FROM TB PIVOT(SUM(TB.COL1) FOR TB.COL2 IN (VAL1 AS NEW_COL1,VAL2 AS NEW_COL2,VAL3 AS NEW_COL3));

pivot必须含有聚合函数

SELECT *
  FROM SCORE_1
PIVOT(SUM(SCORE)
   FOR CNAME IN('CHINESE' AS 语文, 'MATH' AS 数学, 'ENGLISH' AS 英语));

四、SCORE_2==>SCORE_1

1.集合运算

SELECT SNO,'CHINESE' CNAME,语文 score FROM SCORE_2
union ALL
SELECT SNO,'MATH' CNAME,数学 score FROM SCORE_2
union ALL
SELECT SNO,'ENGLISH' CNAME,英语 score FROM SCORE_2;

2.unpivot

语法：SELECT * FROM TB UNPIVOT(NEW_COL1 FOR NEW_COL2 IN (TB.COL1 AS VAL1,TB.COL2 AS VAL2,TB.COL3 AS VAL3));

SELECT *
  FROM SCORE_2 UNPIVOT(SCORE FOR CNAME IN(语文 AS 'CHINESE',
                                          数学 AS 'MATH',
                                          英语 AS 'ENGLISH'));

这篇关于Oracle行列转换的方式的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！