Pivot dan unPivot Table di MySQL DB

Iseng-iseng pada saat liburan imlek kemarin, saya mencoba utak atik query MySQL. Maklum sebelumnya saya memang baru beli buku ‘Belajar DATABASE menggunakan MySQL’ karangan Abdul Kadir hehehe Rencananya emank mau dibaca-baca saat waktu senggang liburan imlek. Meskipun beberapa tahun ini selalu berkutat dengan query SQL (Oracle DB), namun saya masih newbie di MySQL DB lho 😀 Pada kesempatan ini saya akan coba mensharingkan apa yang saya dapat, yaitu penggunaan Ekspresi dalam Query sekaligus implementasinya dalam salah satu solusi dari kasus query yang mungkin sebagian teman-teman sudah tau, yakni Pivot Table (Baris ke Kolom) dan unPivot Table (Kolom ke Baris). Ayo kita mulai…

Contoh ada 1 tabel awal seperti dibawah ini. Kolom TEAM berisi kode TEAM, dimana masing-masing team memiliki personil masing-masing sesuai JABATAN, kemudian terdapat attribute nominal GAJI.

TABEL AWAL

Query:

SELECT * FROM pegawai ORDERY BY 1,2; 

+--------+------------+---------+
| TEAM   | JABATAN    | GAJI    |
+--------+------------+---------+
| 01     | ANALYST    | 2300000 |
| 01     | MANAGER    | 3000000 |
| 01     | MARKETING  | 1700000 |
| 01     | PROGRAMMER | 2000000 |
| 01     | QC         | 1700000 |
| 02     | ANALYST    | 2400000 |
| 02     | MANAGER    | 3500000 |
| 02     | MARKETING  | 1900000 |
| 02     | PROGRAMMER | 2200000 |
| 02     | QC         | 1900000 |
| 03     | ANALYST    | 2200000 |
| 03     | MANAGER    | 3500000 |
| 03     | MARKETING  | 1900000 |
| 03     | PROGRAMMER | 2000000 |
| 03     | QC         | 1500000 |
+--------+------------+---------+

Tujuannya kita akan menkonversi dari baris menjadi Kolom, sehingga menghasilkan output query sbb:

+--------+-----------+---------+------------+---------+---------+
| TEAM   | MARKETING | ANALYST | PROGRAMMER | QC      | MANAGER |
+--------+-----------+---------+------------+---------+---------+
| 01     |   1700000 | 2300000 |    2000000 | 1700000 | 3000000 |
| 02     |   1900000 | 2400000 |    2200000 | 1900000 | 3500000 |
| 03     |   1900000 | 2200000 |    2000000 | 1500000 | 3500000 |
+--------+-----------+---------+------------+---------+---------+

Salah satu contoh query yang bisa kita gunakan adalah sbb:

SELECT TEAM,
  CASE jabatan WHEN 'MARKETING' THEN gaji ELSE 0 END MARKETING,
  CASE jabatan WHEN 'ANALYST' THEN gaji ELSE 0 END ANALYST,
  CASE jabatan WHEN 'PROGRAMMER' THEN gaji ELSE 0 END PROGRAMMER,
  CASE jabatan WHEN 'QC' THEN gaji ELSE 0 END QC,
  CASE jabatan WHEN 'MANAGER' THEN gaji ELSE 0 END MANAGER
FROM pegawai
GROUP BY team
ORDER BY 1

Kita menggunakan ekspresi CASE WHEN THEN END untuk membandingkan nilai kolom JABATAN dari setiap baris record yang dipanggil, apabila sesuai maka akan diambil nilai dari kolom GAJI nya. Kemudian kita juga mengelompokkan berdasarkan kolom TEAM. Dalam pengembangannya kita juga bisa menambahkan fungsi-fungsi Agregat lainnya sesuai yang kita inginkan.

Okey, sekarang kita telah berhasil melakukan Pivot Table, bagaimana kalau kebalikannya unPivot Table? Asumsikan lagi kita memiliki 1 Tabel awal berbentuk seperti hasil dari Pivot. Supaya lebih mudah, kita bisa membuat 1 DATABASE VIEW dari query Pivot kita diatas bernama VIEW_PEGAWAI. Sehingga kita dapat langsung memanggilnya seakan-akan seperti sebuah table, SQL script View sbb:

CREATE VIEW `views` AS select `pegawai`.`team` AS `team`,
     (case `pegawai`.`jabatan` 
        when _utf8'MARKETING' then `pegawai`.`gaji` else 0 end) AS   `MARKETING`,
     (case `pegawai`.`jabatan` 
        when _utf8'ANALYST' then `pegawai`.`gaji` else 0 end) AS `ANALYST`,
     (case `pegawai`.`jabatan` 
        when _utf8'PROGRAMMER' then `pegawai`.`gaji` else 0 end) AS `PROGRAMMER`,
     (case `pegawai`.`jabatan` 
        when _utf8'QC' then `pegawai`.`gaji` else 0 end) AS `QC`,
     (case `pegawai`.`jabatan` 
        when _utf8'MANAGER' then `pegawai`.`gaji` else 0 end) AS `MANAGER`
  from `pegawai` group by `pegawai`.`team` order by 1;

Query untuk unPivot Table adalah sbb:

SELECT A.TEAM,
             IF(B.i=1,'MARKETING',
		IF(B.i=2,'ANALYST',
		   IF(B.i=3,'PROGRAMMER',
                     IF(B.i=4,'QC',
                        IF(B.i=5,'MANAGER','' ))))) JABATAN,
IF(B.i=1,MARKETING,
		IF(B.i=2,ANALYST,
		   IF(B.i=3,PROGRAMMER,
                     IF(B.i=4,QC,
                        IF(B.i=5,MANAGER,0 ))))) GAJI
FROM VIEW_PEGAWAI A,
(SELECT 1 i FROM DUAL
 UNION
 SELECT 2 i FROM DUAL
 UNION
 SELECT 3 i FROM DUAL
 UNION
 SELECT 4 i FROM DUAL
 UNION
 SELECT 5 i FROM DUAL
) B
ORDER BY 1,2

Hasilnya adalah sbb:

+--------+------------+---------+
| TEAM   | jabatan    | gaji    |
+--------+------------+---------+
| 01     | ANALYST    | 2300000 |
| 01     | MANAGER    | 3000000 |
| 01     | MARKETING  | 1700000 |
| 01     | PROGRAMMER | 2000000 |
| 01     | QC         | 1700000 |
| 02     | ANALYST    | 2400000 |
| 02     | MANAGER    | 3500000 |
| 02     | MARKETING  | 1900000 |
| 02     | PROGRAMMER | 2200000 |
| 02     | QC         | 1900000 |
| 03     | ANALYST    | 2200000 |
| 03     | MANAGER    | 3500000 |
| 03     | MARKETING  | 1900000 |
| 03     | PROGRAMMER | 2000000 |
| 03     | QC         | 1500000 |
+--------+------------+---------+

Coba dibandingkan apakah hasil query sama seperti TABEL AWAL diatas? Hasilnya sama persis kan? (kalo tidak sama berarti ada yang salah lho! 😀 )

Pada Query unPivot kita menggunakan ekspresi IF() melakukan Nested IF untuk membandingkan isi nilai kolom-kolom pada setiap baris record. Kemudian kita juga menggunakan Sub Query pada klausa FROM supaya mendapatkan jumlah kolom yang akan kita konversikan menjadi baris, untuk setiap baris record di table Pivot. (pada contoh diatas adalah i=5, karena terdapat 5 jenis kolom JABATAN).

Pivot dan unPivot kadang diperlukan untuk kebutuhan reporting ataupun penyajian data dalam bentuk rangkuman, sehingga informasi yang kita tampilkan akan lebih mudah dibaca oleh user.

Query diatas hanya salah satu contoh pendekatan sederhana, untuk solusi kasus Pivot dan unPivot Table pada MySQL DB. Meskipun begitu masih ada beberapa hal yang belum saya dapatkan disini, seperti bagaimana membuat ekspresi perulangan baris tunggal pada sub query FROM di query unPivot, dimana pada contoh diatas saya masih manual memakai UNION (kalau di Oracle DB ada Klausa-klausa yang lebih sederhana lagi lho untuk kasus unPivot diatas hehehe). Apabila memang ada, ditunggu sharing dan masukan dari teman-teman. Demikianlah sharing saya kali ini. Semoga bermanfaat, Have a Funtastic Days!

NB: Sample DDL dan data pada TABEL AWAL diatas adalah sbb:

DROP TABLE IF EXISTS `pegawai`;
CREATE TABLE `pegawai` (
  `team` varchar(5) default NULL,
  `jabatan` varchar(30) default NULL,
  `gaji` decimal(10,0) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `pegawai` VALUES ('01', 'MARKETING', '1700000');
INSERT INTO `pegawai` VALUES ('01', 'ANALYST', '2300000');
INSERT INTO `pegawai` VALUES ('01', 'PROGRAMMER', '2000000');
INSERT INTO `pegawai` VALUES ('01', 'QC', '1700000');
INSERT INTO `pegawai` VALUES ('01', 'MANAGER', '3000000');
INSERT INTO `pegawai` VALUES ('02', 'MARKETING', '1900000');
INSERT INTO `pegawai` VALUES ('02', 'ANALYST', '2400000');
INSERT INTO `pegawai` VALUES ('02', 'PROGRAMMER', '2200000');
INSERT INTO `pegawai` VALUES ('02', 'QC', '1900000');
INSERT INTO `pegawai` VALUES ('02', 'MANAGER', '3500000');
INSERT INTO `pegawai` VALUES ('03', 'MARKETING', '1900000');
INSERT INTO `pegawai` VALUES ('03', 'ANALYST', '2200000');
INSERT INTO `pegawai` VALUES ('03', 'PROGRAMMER', '2000000');
INSERT INTO `pegawai` VALUES ('03', 'QC', '1500000');
INSERT INTO `pegawai` VALUES ('03', 'MANAGER', '3500000');