oracle 行转列

一、创建表

create table TEST_TB_GRADE
(
  id        NUMBER(10) not null,
  user_name VARCHAR2(20 CHAR),
  course    VARCHAR2(20 CHAR),
  score     FLOAT
)
tablespace TEST
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

二、插入数据

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (1, 'michael', '语文', 78);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (2, 'michael', '数学', 95);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (3, 'michael', '英语', 81);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (4, 'xiaoxiao', '语文', 97);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (5, 'xiaoxiao', '数学', 78);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (6, 'xiaoxiao', '英语', 91);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (7, 'zhangsan', '语文', 80);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (8, 'zhangsan', '数学', 55);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (9, 'zhangsan', '英语', 75);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (10, 'lisi', '语文', 87);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (11, 'lisi', '数学', 66);

insert into test_tb_grade (ID, USER_NAME, COURSE, SCORE)
values (12, 'lisi', '英语', 89);

三、行转列查询sql

select t.user_name as 姓名, 
  sum(decode(t.course, '语文', score,null)) as 语文, 
  sum(decode(t.course, '数学', score,null)) as 数学, 
  sum(decode(t.course, '英语', score,null)) as 英语 
from test_tb_grade t 
group by t.user_name 
order by t.user_name