oracle pivot函数demo

在日常使用中，经常遇到这样的情况，需要将数据库中行转化成列显示，如

转化为

这个时候，我们就需要使用pivot函数

pivot（聚合函数 for 列名 in（类型））

--其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct ranking from temp

需求：统计每天号码卡的激活量。

SELECT T1.V_MONTH,T1.V_AREANAME,T2.TOTAL,TO_CHAR(T1.D_DAY,'DD')
    DD, T1.N_NUM FROM T_DEALER_ACTIVE_REPORT T1 left JOIN (SELECT V_MONTH,V_AREANAME,SUM(N_NUM) TOTAL FROM T_DEALER_ACTIVE_REPORT WHERE 
     1=1 AND V_MONTH=201712  GROUP BY V_MONTH,V_AREANAME) T2  ON 1=1  AND NVL(T1.V_MONTH, 'NULL')=NVL(T2.V_MONTH, 'NULL') AND 
     NVL(T1.V_AREANAME, 'NULL')=NVL(T2.V_AREANAME, 'NULL') WHERE  1=1 AND T1.V_MONTH=201712

结果的意思是20171年12月份地区为新界的号码卡激活一共3张，且在18号激活3张

SELECT V_MONTH,V_AREANAME,TOTAL, NVL(C1,0) C1,NVL(C2,0) C2,NVL(C3,0) C3,NVL(C4,0) C4, NVL(C5,0)C5,NVL(C6,0) C6,NVL(C7,0) C7,
NVL(C8,0) C8,NVL(C9,0) C9,NVL(C10,0) C10,NVL(C11,0) C11,NVL(C12,0) C12,NVL(C13,0) C13,NVL(C14,0) C14,NVL(C15,0) C15,NVL(C16,0) C16,
NVL(C17,0) C17,NVL(C18,0) C18,NVL(C19,0) C19 ,NVL(C20,0) C20, NVL(C21,0) C21,NVL(C22,0) C22,NVL(C23,0) C23,NVL(C24,0) C24,NVL(C25,0) C25,
NVL(C26,0) C26,NVL(C27,0) C27,NVL(C28,0) C28, NVL(C29,0) C29,NVL(C30,0) C30,NVL(C31,0) C31
 FROM (SELECT T1.V_MONTH,T1.V_AREANAME,T2.TOTAL,TO_CHAR(T1.D_DAY,'DD')
    DD, T1.N_NUM FROM T_DEALER_ACTIVE_REPORT T1 left JOIN (SELECT V_MONTH,V_AREANAME,SUM(N_NUM) TOTAL FROM T_DEALER_ACTIVE_REPORT WHERE 
     1=1 AND V_MONTH=201712  GROUP BY V_MONTH,V_AREANAME) T2  ON 1=1  AND NVL(T1.V_MONTH, 'NULL')=NVL(T2.V_MONTH, 'NULL') AND 
     NVL(T1.V_AREANAME, 'NULL')=NVL(T2.V_AREANAME, 'NULL') WHERE  1=1 AND T1.V_MONTH=201712 )
      PIVOT(SUM(N_NUM) FOR DD IN 
     ('01' C1,'02' C2,'03' C3,'04' C4,'05' C5,'06' C6,'07' C7,'08' C8,'09' C9,'10' C10,'11' C11,'12' C12,'13' C13,'14'
      C14,'15' C15,'16' C16,'17' C17,'18' C18,'19' C19,'20' C20,'21' C21,'22' C22,'23' C23,'24' C24,'25' C25,'26' C26,
      '27' C27,'28' C28,'29' C29,'30' C30,'31' C31))

行转列结果如上，