db2 select中使用case替代行转列操作 .

在DB2中进行行转列比较麻烦，这里我在select里用case语法代替行转列操作

 

使用以下查询语句可以实现

 

select t.CHECK_UNIT as 公司,
       t.BOE_DEPT_ID as 部门,
       sum(t.total) as 单据总数,
       sum(t.seven) as 星期天,
       sum(t.one) as 星期一,
       sum(t.two) as 星期二,
       sum(t.three) as 星期三,
       sum(t.four) as 星期四,
       sum(t.five) as 星期五,
       sum(t.six) as 星期六
  from (select bh.CHECK_UNIT,
               bh.BOE_DEPT_ID,
               count(*) as total,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 1) then
                        1
                     end) as seven,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 2) then
                        1
                     end) as one,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 3) then
                        1
                     end) as two,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 4) then
                        1
                     end) as three,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 5) then
                        1
                     end) as four,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 6) then
                        1
                     end) as five,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 7) then
                        1
                     end) as six
          from sie.SIE_BOE_HEADERS bh
         where bh.BOE_DATE > TIMESTAMP('2007-7-20-12.00.00.000000')
         group by bh.CHECK_UNIT, bh.BOE_DEPT_ID, DAYOFWEEK(bh.BOE_DATE)) t
 group by t.CHECK_UNIT, t.BOE_DEPT_ID;