【SQL 学习】行列转换

SQL> conn system/yang as sysdba

已连接。

SQL> conn yang/yang

SQL> create table change (name varchar(10),subject varchar2(20),grade int);

表已创建。

SQL> set timing on

SQL> insert into change values ('Jim','语文',100);

已创建 1 行。

已用时间:  00: 00: 00.00

SQL> insert into change values ('Jim','数学',90);

已用时间:  00: 00: 00.03

SQL> insert into change values ('Jim','英语',99);

SQL> insert into change values ('Lily','语文',80);

已用时间:  00: 00: 00.01

SQL> insert into change values ('Lily','数学',90);

SQL> insert into change values ('Lily','英语',94);

SQL> commit;

提交完成。

SQL> select * from change;

NAME       SUBJECT                   GRADE                                     

---------- -------------------- ----------                                     

Jim        语文                        100                                     

Jim        数学                         90                                     

Jim        英语                         99                                     

Lily         语文                         80                                     

Lily         数学                         90                                     

Lily         英语                         94                                     

已选择6行。

SQL> select name as "姓名" ,

  2  sum(decode(subject,'语文',grade,NULL)) "语文",

  3  sum(decode(subject,'数学',grade,NULL)) "数学",

  4  sum(decode(subject,'英语',grade,NULL)) "英语"

  5  from change

  6  group by name;

姓名             语文       数学       英语                                    

---------- ---------- ---------- ----------                                    

Lily               80         90         94                                    

Jim            100         90         99