【Oracle】行转列

defects表结构：

create table defects(
    id number(4),
    code varchar2(6),
    type varchar2(10) check(type='oper' or type='sql' or type='api'),
    status number(1) check(status=0 or status=1),
    remark nvarchar2(100),
    primary key(id)
)

 

插值：

declare 
   d integer;
   status integer;
begin
for i in 1..1000 loop
    d:=dbms_random.value(1,3);
    status:=dbms_random.value(0,1);

    insert into defects values(i,
                               dbms_random.string('*',6)，
                               decode(d,1,'oper',2,'sql',3,'api'),
                               status,
                   '');
end loop;

commit;

end;
/

// 正常查询

select 
    type,
    count(*) as total,
    sum(decode(status,1,1,0,0)) as completed,
    sum(decode(status,1,0,0,1)) as awiating
from defects
group by type

效果：

SQL> select
  2      type,
  3      count(*) as total,
  4      sum(decode(status,1,1,0,0)) as completed,
  5      sum(decode(status,1,0,0,1)) as awiating
  6  from defects
  7  group by type;

TYPE                      TOTAL  COMPLETED   AWIATING
-------------------- ---------- ---------- ----------
api                         253        126        127
sql                         511        248        263
oper                        236        112        124

 

行转列：

语句：

select
    'total' as class,
    sum(case type when 'oper' then 1 end) as oper,
    sum(case type when 'sql' then 1 end) as sql,
    sum(case type when 'api' then 1 end) as api
from defects
union all
select
    'completed' as class,
    sum(case type when 'oper' then status end) as oper,
    sum(case type when 'sql' then status end) as sql,
    sum(case type when 'api' then status end) as api
from defects
union all
select
    'awiating' as class,
    sum(case type when 'oper' then decode(status,1,0,0,1) end) as oper,
    sum(case type when 'sql' then decode(status,1,0,0,1) end) as sql,
    sum(case type when 'api' then decode(status,1,0,0,1) end) as api
from defects

效果：

SQL> select
  2      'total' as class,
  3      sum(case type when 'oper' then 1 end) as oper,
  4      sum(case type when 'sql' then 1 end) as sql,
  5      sum(case type when 'api' then 1 end) as api
  6  from defects
  7  union all
  8  select
  9      'completed' as class,
 10      sum(case type when 'oper' then status end) as oper,
 11      sum(case type when 'sql' then status end) as sql,
 12      sum(case type when 'api' then status end) as api
 13  from defects
 14  union all
 15  select
 16      'awiating' as class,
 17      sum(case type when 'oper' then decode(status,1,0,0,1) end) as oper,
 18      sum(case type when 'sql' then decode(status,1,0,0,1) end) as sql,
 19      sum(case type when 'api' then decode(status,1,0,0,1) end) as api
 20  from defects;

CLASS                    OPER        SQL        API
------------------ ---------- ---------- ----------
total                     236        511        253
completed                 112        248        126
awiating                  124        263        127

 

END