【Oracle 11g】(二) 行转列 case when

【Oracle 11g】(二) 行转列 case when

一、行转列（case when）

SELECT 
    max(CASE WHEN deptno = 10 THEN cnt ELSE 0 END) "10",
    max(CASE WHEN deptno = 20 THEN cnt ELSE 0 END) "20",
    max(CASE WHEN deptno = 30 THEN cnt ELSE 0 END) "30",
    SUM(cnt) 总人数 
FROM (
    SELECT deptno, COUNT(empno) cnt
    FROM emp
    GROUP BY deptno
    )

需求：请统计每个月份，每个手机品牌的销量

select * from BRAND          ;  --品牌表
select * from ORDER_DETAIL   ;  --订单明细表
select * from PHONE_MODEL    ;  --机型表

SELECT brand_name,
    max(CASE WHEN MONTH='2018-03' THEN sales ELSE 0 END) as "2018-03",
    max(CASE WHEN MONTH='2018-04' THEN sales ELSE 0 END) as "2018-04",
    max(CASE WHEN MONTH='2018-05' THEN sales ELSE 0 END) as "2018-05",
    max(CASE WHEN MONTH='2018-06' THEN sales ELSE 0 END) as "2018-06"
FROM (
    SELECT to_char(order_date, 'yyyy-mm')as MONTH,
           c.brand_name,
           sum(amount) sales
    FROM ORDER_DETAIL a,
         PHONE_MODEL b,
         BRAND c
    WHERE a.mp_no=b.mp_no AND
         b.brand_no=c.brand_no
    GROUP BY to_char(order_date, 'yyyy-mm'), brand_name
)t
GROUP BY brand_name;