Oracle行转列代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】 Oracle行转列

作者:Dezai.CN / 发布于2011/12/28/ 1802

<div>create table pivot(
pivot_id number(9) not null,
year char(4) not null,
quarter char(1) not null,
amount number(9,2) not null,
constraint pivot_pk primary key(pivot_id),
constraint pivot_ak unique (year,quarter)
);</div>
<div>/*insert test data*/
insert into pivot (pivot_id,year,quarter,amount) values(1,'2008','1',2085.45);
insert into pivot (pivot_id,year,quarter,amount) values(2,'2008','2',5899.90);
insert into pivot (pivot_id,year,quarter,amount) values(3,'2008','3',46899.23);
insert into pivot (pivot_id,year,quarter,amount) values(4,'2008','4',554.55);</div>
<div>insert into pivot (pivot_id,year,quarter,amount) values(5,'2007','1',69878.23);
insert into pivot (pivot_id,year,quarter,amount) values(6,'2007','2',5568.78);
insert into pivot (pivot_id,year,quarter,amount) values(7,'2007','3',4566.00);
insert into pivot (pivot_id,year,quarter,amount) values(8,'2007','4',56658.55);</div>
<div>insert into pivot (pivot_id,year,quarter,amount) values(9,'2006','1',3565.36);
insert into pivot (pivot_id,year,quarter,amount) values(10,'2006','2',4789.56);
insert into pivot (pivot_id,year,quarter,amount) values(11,'2006','3',9865.78);
insert into pivot (pivot_id,year,quarter,amount) values(12,'2006','4',469852.36);</div>
<div>select year,
(case quarter when '1' then amount else 0 end) as Q1,
(case quarter when '2' then amount else 0 end) as Q2,
(case quarter when '3' then amount else 0 end) as Q3,
(case quarter when '4' then amount else 0 end) as Q4
from pivot;</div>
<div>select year,
sum(case quarter when '1' then amount else 0 end) as Q1,
sum(case quarter when '2' then amount else 0 end) as Q2,
sum(case quarter when '3' then amount else 0 end) as Q3,
sum(case quarter when '4' then amount else 0 end) as Q4
from pivot group by year;</div>
<div>select p.*, (Q1+Q2+q3+Q4) as total
from(
select year, 
sum(case quarter when '1' then amount else 0 end) as Q1,
sum(case quarter when '2' then amount else 0 end) as Q2,
sum(case quarter when '3' then amount else 0 end) as Q3,
sum(case quarter when '4' then amount else 0 end) as Q4
from pivot group by year
)p;</div>
<div></div>

试试其它关键字

　行转列　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)