行列转换并且进行行列数据统计 rollup

--具体的行列转换并且进行行列数据统计

create table tmpA 
( 
Dept char(3) , 
Sect char(3) , 
line char(3) , 
Line_Desc varchar(30) , 
Title_code char(3) , 
Title_Desc varchar(30) , 
Headcount int 
);

-- 加入一些数据记录

Insert into tmpA values ('DA' , 'S1' , 'La' , 'Line a ' , 'T1' , 'Title Desc 1', 3 );
Insert into tmpA values ('DA' , 'S1' , 'La' , 'Line a ' , 'T2' , 'Title Desc 2' , 3 );
Insert into tmpA values ('DA' , 'S1' , 'Lb' , 'Line b ' , 'T1' , 'Title Desc 1' , 3 );
Insert into tmpA values ('DA' , 'S1' , 'Lb' , 'Line b ' , 'T2' , 'Title Desc 2' , 3 );
Insert into tmpA values ('DA' , 'S2' , 'La' , 'Line a ' , 'T1' , 'Title Desc 1' , 3 );
Insert into tmpA values ('DA' , 'S2' , 'Lb' , 'Line b ' , 'T1' ,  'Title Desc 1' , 3 );
Insert into tmpA values ('DB' , 'S1' , 'Lb' , 'Line b ' , 'T1' ,  'Title Desc 1' , 3 );
Insert into tmpA values ('DB' , 'S2' , 'La' , 'Line a ' , 'T1' ,  'Title Desc 1' , 3 );
Insert into tmpA values ('DB' , 'S2' , 'Lb' , 'Line b ' , 'T1' ,  'Title Desc 1' , 3 );
Insert into tmpA values ('DC' , 'S1' , 'La' , 'Line a ' , 'T1' ,  'Title Desc 1' , 3 );
Insert into tmpA values ('DC' , 'S2' , 'Lb' , 'Line b ' , 'T1' ,  'Title Desc 1' , 3);
select * from tmpA;

-- 行转列一 行统计

select dept,decode(sect,'S1 ',sum(Headcount),0) as s1,
decode(sect,'S2 ',sum(Headcount),0) as s2,
sum(Headcount) as 行合计 
from tmpA  
group by dept,sect,Headcount;

-- 行转列二 行统计                  

select dept,sum(decode(sect,'S1 ',Headcount,0)) as s1,
sum(decode(sect,'S2 ',Headcount,0)) as s2,
sum(Headcount) as 行合计 
from tmpA  
group by dept,Headcount;  

      
-- 列统计

select dept,decode(sect,'S1 ',sum(TO_NUMBER(Headcount)),'0') as s1,
decode(sect,'S2 ',sum(TO_NUMBER(Headcount)),'0') as s2,
sum(TO_NUMBER(Headcount)) as 行合计 
from tmpA  
group by sect,rollup(dept,headcount);

      
-- 行列统计

select dept,sum(decode(sect,'S1 ',Headcount,0)) as s1,
sum(decode(sect,'S2 ',Headcount,0)) as s2,
sum(Headcount) as 行合计 
from tmpA  
group by rollup(dept);