行变列

一个SQL题目:

           有一张表timetable
                  教师号  星期号 是否有课
                      １        ２      有
                      １        ３      有
                      ２        １      有
                      ３        ２      有
                      １        ２      有
           写个SQL语句表这张表变成如下结构的表:
                  教师号 星期一 星期二 星期三
                     １       ２      １ 
                     ２       １   
                     ３       １
                     星期下面的数字表示：对应的教师在星期几已经排的课数.

           看到这个题目第一眼,不知道如下下手,去坛子问了,有了第一个答案:
             1,创建表:
                create table timetable(teacherno number,weekno number,course varchar2(15));
             2,插入数据:
               insert into timetable values(&teacherno,&weekno,&course);
               按提示输入需要插入的值,也可以在插入的时候直接把值写到插入语句中
             3,commit;
              本题解决方法1:
             create table test as 
                                   select teacherno as 教师号,
                                              sum(case when weekno=1 and course='有' then 1 end) as 星期一,
                                              sum(case when weekno=2 and course='有' then 1 end) as 星期二,
                                              sum(case when weekno=3 and course='有' then 1 end) as 星期三
                                   from timetable 
                                   group by  teacherno;
                   查询结果:
              SQL> select * from test;
                           教师号        星期一     星期二     星期三
                          ----------   ----------   ----------   ----------
                                1                           2             1
                                2           1
                                3                           1
               满足题目要求:这个解决方法是利用case when condition result 搜索case语句使用的相关条件来确定返回值
               这个方法是坛子里面的人告诉,同时告诉可以使用oracle 的专有函数decode来处理:
               下面的方法是我自己弄出来的,基本也能满足要求了,不过使用了两次decode,因为不晓得怎么用decode一次去比较两个条件
                 1,表及数据利旧.
                 2,SQL语句
                        create table test000  as 
                                   select    teacherno as 教师号,
                                                sum(decode(weekno,1,decode(course,'有',1))) 星期一,
                                                sum(decode(weekno,2,decode(course,'有',1))) 星期二,
                                                sum(decode(weekno,3,decode(course,'有',1))) 星期三
                                  from timetable 
                                 group by  teacherno;
                3,查询结果:
                       SQL> create table test000  as 
                                 2                select    teacherno as 教师号,
                                 3                             sum(decode(weekno,1,decode(course,'有',1))) 星期一,
                                 4                             sum(decode(weekno,2,decode(course,'有',1))) 星期二,
                                 5                             sum(decode(weekno,3,decode(course,'有',1))) 星期三
                                 6                from timetable 
                                 7                group by  teacherno;
                               Table created.
                      SQL> select * from test000;
                                        教师号     星期一     星期二     星期三
                                      ----------  ----------   ----------   ----------
                                            1                            2              1
                                            2             1
                                            3                            1
                            说明:decode 函数是oracle专有的非标准SQL中
   
                            一次记录学习的过程,达人门如有更好的方法,望不吝赐教!!