Laporan Tahunan di Mysql - Pivot Table

Jumpa lagi di catatan fey. Kali ini saya akan menambahkan sebuat catatan baru  mengenai Pivot Tabel di MySql, biasanya pivot table sangat mudah di gunakan di Microsoft Excel. nah kali ini kita akan mencobanya di MySql.

contoh permasalahannya seperti ini kita mempunyai data seperti ini:

dan laporan yg akan kita buat seperti ini

buat sebuah database dengan nama tes1, dengan 2 buah field yaitu date dan nilai masukkan bebrapa contoh data

 insert into tes1 value
('2011-01-01',1000),
('2011-02-1',2000),
('2011-03-1',3000),
('2011-04-1',4000),
('2011-05-1',5000),
('2011-06-1',6000),
('2011-07-1',7000),
('2011-08-1',8000),
('2011-09-1',9000),
('2011-10-1',10000),
('2011-11-1',11000),
('2011-12-1',12000),
('2012-01-1',13000),
('2012-02-1',14000),
('2012-03-1',15000),
('2012-04-1',16000),
('2012-05-1',17000),
('2012-06-1',18000),
('2012-07-1',19000),
('2012-08-1',20000),
('2012-09-1',21000),
('2012-10-1',22000),
('2012-11-1',23000),
('2012-12-1',24000),
('2013-01-1',25000),
('2013-02-1',26000),
('2013-03-1',27000),
('2013-04-1',28000),
('2013-05-1',29000),
('2013-06-1',30000),
('2013-07-1',31000),
('2013-08-1',32000),
('2013-09-1',33000),
('2013-10-1',34000),
('2013-11-1',35000),
('2013-12-1',36000);


sehingga hasinya

maka untuk penampilannya pivot tablenya adalah :

select
YEAR(date),
sum(if(Month(date)=1,nilai,0)) as jan,
sum(if(Month(date)=2,nilai,0)) as feb,
sum(if(Month(date)=3,nilai,0)) as mar,
sum(if(Month(date)=4,nilai,0)) as apr,
sum(if(Month(date)=5,nilai,0)) as mei,
sum(if(Month(date)=6,nilai,0)) as jun,
sum(if(Month(date)=7,nilai,0)) as jul,
sum(if(Month(date)=8,nilai,0)) as ags,
sum(if(Month(date)=9,nilai,0)) as sep,
sum(if(Month(date)=10,nilai,0)) as okt,
sum(if(Month(date)=11,nilai,0)) as nop,
sum(if(Month(date)=12,nilai,0)) as des,
sum(nilai) as total
from tes1 group by YEAR(date);



Demikian catatan fey kali ini. jikalau ada kata-kata yang kurang atau salah saya mohon maaf. semoga bermanfaat.