【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