行转列、列转行演示

1、行转列

--建表
create table TEST2 
( 
  ID        NUMBER(10) not null, 
  USER_NAME VARCHAR2(20 CHAR), 
  COURSE    VARCHAR2(20 CHAR), 
  SCORE     FLOAT 
) ;

--插入基础数据

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('1', '张三', '语文', 78);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('2', '张三', '数学', 95);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('3', '张三', '英语', 81);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('4', '李四', '语文', 97);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('5', '李四', '数学', 78);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('6', '李四', '英语', 91);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('7', '王五', '语文', 68);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('8', '王五', '数学', 76);

insert into TEST2 (ID, USER_NAME, COURSE, SCORE)
values ('9', '王五', '英语', 83);

-- see table
SELECT * FROM test2;

 

--行转列测试
SELECT T.USER_NAME,
       SUM(DECODE(T.COURSE, '语文', SCORE, NULL)) AS CHINESE,
       SUM(DECODE(T.COURSE, '数学', SCORE, NULL)) AS MATH,
       SUM(DECODE(T.COURSE, '英语', SCORE, NULL)) AS ENGLISH
  FROM TEST2 T
 GROUP BY T.USER_NAME

 

2、列转行演示：

--建表
create table TEST3(  
      USER_NAME VARCHAR2(20 CHAR),   
      CHINESE    FLOAT,   
      MATH     FLOAT   ,
      ENGLISH FLOAT
    );
    
--插入基础数据   
insert into test3 (USER_NAME, CHINESE, MATH, ENGLISH) VALUES ('王五', 68, 76, 83);

insert into test3 (USER_NAME, CHINESE, MATH, ENGLISH) values ('李四', 97, 78, 91);

insert into test3 (USER_NAME, CHINESE, MATH, ENGLISH) values ('张三', 78, 95, 81);

--see table
SELECT * FROM test3;

--列转行测试
SELECT USER_NAME, '语文' COURSE, CHINESE AS SCORE
  FROM TEST3
UNION
SELECT USER_NAME, '数学' COURSE, MATH AS SCORE
  FROM TEST3
UNION
SELECT USER_NAME, '英语' COURSE, ENGLISH AS SCORE
  FROM TEST3
 ORDER BY USER_NAME, COURSE

转载于:https://www.cnblogs.com/fei-yang/p/4019458.html