關于oracle采用pivot函數列轉行後一些列的值為null處理

假設現在有三張表，一張類别表，一張員工表，一張交易明細表

create table kinds( name varchar2(32) ,code char(2));
insert into kinds(name, code) values('name1','01');
insert into kinds(name, code) values('name1','02');
insert into kinds(name, code) values('name1','03');
insert into kinds(name, code) values('name1','04');
create table trade_dtl(empid number(5),empname varchar2(9),kind_code char(2),amount number(10,2),trade_date varchar2(8));
insert into trade_dtl(empid, empname, kind_code, amount, trade_date) values(1,'張三','01',100,'20151202');
insert into trade_dtl(empid, empname, kind_code, amount, trade_date) values(1,'張三','02',100,'20151102');
insert into trade_dtl(empid, empname, kind_code, amount, trade_date) values(2,'李四','03',100,'20151102');
insert into trade_dtl(empid, empname, kind_code, amount, trade_date) values(2,'李四','04',100,'20151202');
           

現在要看每個人每種商品彙總資訊，可以寫如下語句

select * 
  from(select d.empid,d.empname,d.kind_code,d.amount 
         from trade_dtl d) 
  pivot(sum(amount) as amount,count(1) as num for kind_code in ('01' name1,'02' name2,'03' name3,'04' name4)) 
           

但是，不管是李四還是張三都沒有所有類别的交易明細，至少缺了一種，比如：張三從trade_dtl表中取的資料就不含‘03’這種類别的交易明細

而且類别這張表是可變的，是以要讓null顯示為0，則要改為：

select *
  from (select t1.code, t1.empid, t1.empname, nvl(t2.amount, 0) as amount
          from (select d.code, d.name, e.empid, e.empname
                  from employee e, kinds d) t1
          left join trade_dtl t2
            on t1.empid = t2.empid
           and t1.code = t2.kind_code) 
   pivot(sum(amount) as amount, count(1) as num for code in('01' name1, '02' name2, '03' name3, '04' name4))
           

這個改動的思路就是把kinds表作為主表，但這個寫法我個人覺得不是太好，歡迎批評指正