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

最新推荐文章于 2023-02-07 15:07:43 发布

sandykwx

最新推荐文章于 2023-02-07 15:07:43 发布

阅读量307

点赞数

分类专栏： Oracle SQL

SQL 同时被 2 个专栏收录

20 篇文章 0 订阅

订阅专栏

Oracle

14 篇文章 0 订阅

订阅专栏

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

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);