查询行转列

SELECT to_date(to_char(last_day(add_months(SYSDATE, -2))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date(to_char(last_day(add_months(SYSDATE, -1)),'yyyy-mm-dd'),'yyyy-mm-dd')   FROM dual;

SELECT to_date(to_char(last_day(add_months(SYSDATE, -3))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date(to_char(last_day(add_months(SYSDATE, -2)),'yyyy-mm-dd'),'yyyy-mm-dd')   FROM dual;

SELECT to_date(to_char(last_day(add_months(SYSDATE, -4))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),to_date(to_char(last_day(add_months(SYSDATE, -3)),'yyyy-mm-dd'),'yyyy-mm-dd')   FROM dual;

 

1: select sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, 0)),'yyyy-mm') then quantity else 0 end) currentmonthquantity, 

          sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, -1)),'yyyy-mm') then quantity else 0 end) , 

          sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, -2)),'yyyy-mm') then quantity else 0 end), 

          sum(case when to_char(billingdate,'yyyy-mm') =to_char(last_day(add_months(SYSDATE, -3)),'yyyy-mm') then quantity else 0 end) , 

          prodid,branchid,sum(quantity) quantity From (

             select * From tb_gos_sale_salestockoutdet

             where billingdate between to_date(to_char(last_day(add_months(SYSDATE, -4))+1,'yyyy-mm-dd'),'yyyy-mm-dd') and sysdate

         ) group by prodid,branchid

         having prodid='SPH00013655' 

 

 

2:   select * From (

   select to_char(billingdate,'yyyy-mm') billdate,prodid,branchid,sum(quantity) quantity From (

             select * From tb_gos_sale_salestockoutdet

             where billingdate between to_date(to_char(last_day(add_months(SYSDATE, -4))+1,'yyyy-mm-dd'),'yyyy-mm-dd') and sysdate

         ) group by to_char(billingdate,'yyyy-mm'),prodid,branchid

         having prodid='SPH00013655'

         )

          pivot 

          (

             sum(quantity)

             for billdate in ('2011-03' as 三月销售,'2011-04','2011-05','2011-06')

          )