【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