【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