Query Rekap Nilai (Pivot Nilai)

Contoh syntax query ini adalah untuk menghitung atau untuk merekap data nilai peserta lomba dalam bentuk pivot.

Format laporan pivot sering digunakan dalam perangkat lunak spreadsheet seperti Microsoft Excel atau Google Sheets, serta dalam alat bisnis dan analisis data lainnya.

Query Pivot

Siapkan tabel sebagai bahan untuk dibuat laporan pivot

CREATE TABLE `peserta` (
	`urut` INT(11) NOT NULL AUTO_INCREMENT,
	`no_pelanggan` VARCHAR(15) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
	`nama` VARCHAR(35) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci'
PRIMARY KEY (`urut`) USING BTREE,
	INDEX `index` (`nama`, `status`, `status_member`, `id_template_pesan`, `no_pelanggan`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=4
;

CREATE TABLE `kriteria` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`nama_kriteria` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`keterangan` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`max_nilai` FLOAT(5,2) NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=5
;

CREATE TABLE `penilaian` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`id_acara` INT(11) NULL DEFAULT NULL,
	`id_juri` INT(11) NULL DEFAULT NULL,
	`id_kriteria` INT(11) NULL DEFAULT NULL,
	`id_peserta` INT(11) NULL DEFAULT NULL,
	`nilai` FLOAT(5,2) NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=13
;

CREATE TABLE `opr` (
	`ID_OPR` INT(11) NULL DEFAULT '0',
	`PASSWORD` VARCHAR(16) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
	`STATUS` VARCHAR(10) NULL DEFAULT 'OPERATOR' COLLATE 'latin1_swedish_ci',
	`NAMA_LENGKAP` VARCHAR(50) NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
	`NAMA` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
	`ALAMAT` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`TELP` VARCHAR(17) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	PRIMARY KEY (`NAMA`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

Query pertama kita coba joinkan ketiga tabel tersebut dengan syntax berikut

SELECT 
o.ID_OPR, o.NAMA_LENGKAP AS nama_juri,c.nama AS nama_peserta,
p.id_acara, p.id_juri, p.id_kriteria, p.nilai
FROM penilaian p
INNER JOIN opr o
ON p.id_juri = o.ID_OPR
INNER JOIN contact c
ON p.id_peserta = c.urut;

Hasilnya akan seperti ini

Sekarang kita buat query rekap nilai berdasarkan kriteria dan juri

/* NIlai per kategori */
SELECT c.nama AS nama_peserta, o.NAMA_LENGKAP AS nama_juri,
SUM(if(p.id_kriteria = 1, p.nilai,0)) AS 'Kat1',
SUM(if(p.id_kriteria = 2, p.nilai,0)) AS 'Kat2',
SUM(if(p.id_kriteria = 3, p.nilai,0)) AS 'Kat3',
SUM(if(p.id_kriteria = 4, p.nilai,0)) AS 'Kat4',
SUM(p.nilai) AS 'Total'
FROM penilaian p
INNER JOIN opr o
ON p.id_juri = o.ID_OPR
INNER JOIN contact c
ON p.id_peserta = c.urut
GROUP BY c.urut, o.ID_OPR
ORDER BY SUM(p.nilai) desc; 

Hasilnya akan seperti ini

Dan terakhir kita buat query peringkat dalam bentuk pivot nilai

/* Nilai per juri */
SELECT c.nama AS nama_peserta, 
SUM(if(o.ID_OPR  = 1, p.nilai,0)) AS 'Juri1',
SUM(if(o.ID_OPR  = 2, p.nilai,0)) AS 'Juri2',
SUM(if(o.ID_OPR  = 3, p.nilai,0)) AS 'Juri3',
SUM(if(o.ID_OPR  = 4, p.nilai,0)) AS 'Juri4',
SUM(p.nilai) AS 'Total'
FROM penilaian p
INNER JOIN opr o
ON p.id_juri = o.ID_OPR
INNER JOIN contact c
ON p.id_peserta = c.urut
GROUP BY c.urut
ORDER BY SUM(p.nilai) desc; 

Hasilnya akan seperti ini

Mudah bukan cara membuat query dalam bentuk pivot? kalau masih ada yang bingung silahkan ikutin diskusi terbuka tentang query yang sering diadakan di channel rhantech.

Sekian mudah-mudahan beramanfaat, terima kasih.