Cara menggunakan pivot mysql adalah

Question

Sayangnya MySQL tidak memiliki fungsi PIVOT yang pada dasarnya adalah apa yang Anda coba lakukan. Jadi, Anda harus menggunakan fungsi agregat dengan pernyataan CASE:

select pt.partner_name,
  count(case when pd.product_name = 'Product A' THEN 1 END) ProductA,
  count(case when pd.product_name = 'Product B' THEN 1 END) ProductB,
  count(case when pd.product_name = 'Product C' THEN 1 END) ProductC,
  count(case when pd.product_name = 'Product D' THEN 1 END) ProductD,
  count(case when pd.product_name = 'Product E' THEN 1 END) ProductE
from partners pt
left join sales s
  on pt.part_id = s.partner_id
left join products pd
  on s.product_id = pd.prod_id
group by pt.partner_name

Lihat SQL Fiddle dengan Demo

Karena Anda tidak tahu Produk, Anda mungkin ingin melakukan ini secara dinamis. Ini dapat dilakukan dengan menggunakan pernyataan yang disiapkan.

Tabel pivot dinamis (mengubah baris menjadi kolom)

Kode Anda akan terlihat seperti ini:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when Product_Name = ''',
      Product_Name,
      ''' then 1 end) AS ',
      replace(Product_Name, ' ', '')
    )
  ) INTO @sql
from products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' from partners pt
left join sales s
  on pt.part_id = s.partner_id
left join products pd
  on s.product_id = pd.prod_id
group by pt.partner_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Lihat SQL Fiddle dengan Demo

Mungkin perlu dicatat bahwa GROUP_CONCAT secara default terbatas pada 1024 byte. Anda dapat mengatasi ini dengan menetapkannya lebih tinggi selama durasi prosedur Anda, yaitu. SET @@group_concat_max_len = 32000;

Bulan lalu dapat tugas buat menampilkan data yang beberapa jumlah kolomnya dinamis menggunakan dB SQL Server yang melibatkan beberapa tabel, setelah berhasil pada SQL Server akhirnya sekalian nyoba juga pada dB MySQL. Istilahnya dikenal dengan pivot table. Desain dB yang digunakan pada contoh ini adalah :

Table Pivot pada MySQL

Buat dulu beberapa tabel di dB yang diperlukan untuk mendukung query tersebut. Buat tabel Pelajaran, Kelas, Peserta dan Peserta_has_Pelajaran dengan syntax berikut :

-- -----------------------------------------------------
-- Table `Pelajaran`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Pelajaran` (
`idPelajaran` VARCHAR(3) NOT NULL,
`NamaPelajaran` VARCHAR(45) NULL,
`Keterangan` VARCHAR(50) NULL,
PRIMARY KEY (`idPelajaran`));
-- -----------------------------------------------------
-- Table `kelas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kelas` (
`kodeKelas` INT NOT NULL,
`NamaKelas` VARCHAR(15) NULL,
PRIMARY KEY (`kodeKelas`));
-- -----------------------------------------------------
-- Table `Peserta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Peserta` (
`IdPeserta` VARCHAR(3) NOT NULL,
`NamaPeserta` VARCHAR(45) NULL,
`kelas_kodeKelas` INT NOT NULL,
PRIMARY KEY (`IdPeserta`),
INDEX `fk_Perserta_kelas1_idx` (`kelas_kodeKelas` ASC),
CONSTRAINT `fk_Perserta_kelas1`
FOREIGN KEY (`kelas_kodeKelas`)
REFERENCES `kelas` (`kodeKelas`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table `Peserta_has_Pelajaran`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Peserta_has_Pelajaran` (
`Peserta_IdPeserta` VARCHAR(3) NOT NULL,
`Pelajaran_idPelajaran` VARCHAR(3) NOT NULL,
PRIMARY KEY (`Peserta_IdPeserta`, `Pelajaran_idPelajaran`),
INDEX `fk_Peserta_has_Pelajaran_Pelajaran1_idx` 
   (`Pelajaran_idPelajaran` ASC),
INDEX `fk_Peserta_has_Pelajaran_Perserta_idx` 
   (`Peserta_IdPeserta` ASC),
CONSTRAINT `fk_Peserta_has_Pelajaran_Perserta`
FOREIGN KEY (`Peserta_IdPeserta`)
REFERENCES `Peserta` (`IdPeserta`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Peserta_has_Pelajaran_Pelajaran1`
FOREIGN KEY (`Pelajaran_idPelajaran`)
REFERENCES `Pelajaran` (`idPelajaran`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

Isi tabel dengan beberapa baris data :

INSERT INTO `pelajaran` VALUES ('001','Matematika', null);
INSERT INTO `pelajaran` VALUES ('002', 'Kimia', null);
INSERT INTO `pelajaran` VALUES ('003', 'Fisika', null);
INSERT INTO `pelajaran` VALUES('004', 'Bhs Arab', null);
INSERT INTO `kelas` VALUES ('7', 'VII');
INSERT INTO `kelas` VALUES ('8', 'VIII');
INSERT INTO `kelas` VALUES ('9', 'IX');
INSERT INTO `peserta` VALUES ('001', 'orang1', '7');
INSERT INTO `peserta` VALUES ('002', 'orang2', '8');
INSERT INTO `peserta` VALUES ('003', 'orang3', '9');
INSERT INTO `peserta` VALUES ('004', 'orang4', '7');
INSERT INTO `peserta` VALUES ('005', 'orang5', '8');
INSERT INTO `peserta` VALUES ('006', 'orang6', '9');
INSERT INTO `peserta` VALUES ('007', 'orang7', '9');
INSERT INTO `peserta` VALUES ('008', 'orang8', '7');
INSERT INTO `peserta` VALUES ('009', 'orang9', '7');
INSERT INTO `peserta_has_pelajaran` VALUES ('001', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('001', '003');
INSERT INTO `peserta_has_pelajaran` VALUES ('002', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('002', '002');
INSERT INTO `peserta_has_pelajaran` VALUES ('003', '003');
INSERT INTO `peserta_has_pelajaran` VALUES ('003', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('004', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('004', '002');
INSERT INTO `peserta_has_pelajaran` VALUES ('004', '003');
INSERT INTO `peserta_has_pelajaran` VALUES ('005', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('006', '003');
INSERT INTO `peserta_has_pelajaran` VALUES ('007', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('007', '003');
INSERT INTO `peserta_has_pelajaran` VALUES ('008', '003');
INSERT INTO `peserta_has_pelajaran` VALUES ('009', '001');
INSERT INTO `peserta_has_pelajaran` VALUES ('009', '002');
INSERT INTO `peserta_has_pelajaran` VALUES ('009', '003');

Selanjutnya adalah membuat query agar hasil akhirnya seperti berikut :

Kelas	Total Peseta	Matematika	Kimia	Fisika	Bhs Arab
VII	9	3	2	4	0
VIII	3	2	1	0	0
IX	5	2	0	3	0

Deklarasikan dulu variable di session yang akan menyimpan sql query sementara :

set @dinamicPivotQuery = NULL;

Dapatkan semua nama-nama kolom yang ingin ditampilkan secara dinamis, dalam contoh ini nama-nama pelajaran(pj.NamaPelajaran) yang ada di table pelajaran dan isi kolomnya merupakan jumlah pelajar (p.jum) yang mengikuti pelajaran tersebut :

Select 
group_concat(
distinct concat(
'MAX(if(p.NamaPelajaran = ''', pj.NamaPelajaran,
''', p.jum, 0)) AS ''', pj.NamaPelajaran, ''''
)
) INTO @dinamicPivotQuery
from pelajaran pj;

Sebelum memabangun pivot-nya. Kita buat dulu sub query (k) yang menampilkan kelas dan total pesertanya :

SELECT p.kelas_kodeKelas, count(php.Peserta_IdPeserta) as jumPeserta
FROM peserta p left outer join peserta_has_pelajaran php 
     on p.IdPeserta = php.Peserta_IdPeserta
Group By p.kelas_kodeKelas

Selanjutnya sub query (p) yang menampilkan kelas, pelajaran dan jumlah pesertanya :

SELECT
k.NamaKelas, count(php.Peserta_IdPeserta) as jumPeserta
FROM
kelas k
left outer join peserta p ON k.kodeKelas = p.kelas_kodeKelas
left outer join peserta_has_pelajaran php 
   ON p.IdPeserta = php.Peserta_IdPeserta
group by k.kodeKelas

Terakhir adalah menggabungkan sub-sub query menjadi satu query yang akan disimpan di variabel dinamicPivotQuery yang sudah dibuat sebelumnya :

SET @dinamicPivotQuery = CONCAT(
   'Select p.NamaKelas as Kelas, jumPeserta as TotalPeserta, ',
   @dinamicPivotQuery, '
from(
 SELECT k.NamaKelas, count(php.Peserta_IdPeserta) as jumPeserta 
 FROM kelas k left outer join peserta p 
   on k.kodeKelas=p.kelas_kodeKelas
 left outer join peserta_has_pelajaran php 
   on p.IdPeserta = php.Peserta_IdPeserta
 group by k.kodeKelas
) k left outer join (
 select k.kodeKelas, k.NamaKelas, pj.NamaPelajaran, 
  count(p.IdPeserta) as jum
 from kelas k left outer join peserta p 
   on k.kodeKelas = p.kelas_kodeKelas
 left outer join peserta_has_pelajaran php 
   on p.IdPeserta=php.Peserta_IdPeserta
 left outer join pelajaran pj 
   on php.Pelajaran_idPelajaran=pj.idPelajaran
 group by k.NamaKelas, pj.NamaPelajaran
) p on k.NamaKelas=p.NamaKelas
GROUP BY p.kodeKelas'
);

Jika ditampilkan isi dari dinamicPivotQuery adalah :

Select p.NamaKelas as Kelas, jumPeserta as TotalPeserta,
 MAX(if(p.NamaPelajaran = 'Matematika', p.jum, 0)) AS 'Matematika',
 MAX(if(p.NamaPelajaran = 'Kimia', p.jum, 0)) AS 'Kimia',
 MAX(if(p.NamaPelajaran = 'Fisika', p.jum, 0)) AS 'Fisika',
 MAX(if(p.NamaPelajaran = 'Bhs Arab', p.jum, 0)) AS 'Bhs Arab'
From
(
 SELECT k.NamaKelas, count(php.Peserta_IdPeserta) as jumPeserta
 FROM kelas k left outer join peserta p 
   on k.kodeKelas=p.kelas_kodeKelas
 left outer join peserta_has_pelajaran php 
   on p.IdPeserta = php.Peserta_IdPeserta
 group by k.kodeKelas
) k left outer join (
 select k.kodeKelas, k.NamaKelas, pj.NamaPelajaran, count(p.IdPeserta) as jum
 from kelas k left outer join peserta p on k.kodeKelas = p.kelas_kodeKelas
 left outer join peserta_has_pelajaran php 
   on p.IdPeserta=php.Peserta_IdPeserta
 left outer join pelajaran pj on php.Pelajaran_idPelajaran=pj.idPelajaran
 group by k.NamaKelas, pj.NamaPelajaran
) p on k.NamaKelas=p.NamaKelas
GROUP BY p.kodeKelas

Dan untuk mendapatkan hasil query pivot table kita jalankan isi variable dinamicPivotQuery :

PREPARE stmt FROM @dinamicPivotQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Dimana PREPARE adalah menyiapkan statement yang akan dieksekusi; EXECUTE adalah untuk menjalankan statement yang sudah disiapkan pada PREPARE; DEALLOCATE PREPARE adalah untuk membebaskan statement setelah dieksekusi, ini sebenarnya tidak perlu kita tuliskan lagi, karena sudah secara default di-dealokasi oleh server.
Dan jika dijalankan hasilnya adalah :

Table Pivot pada SQL Server

Buat table yang butuhkan :

-- -----------------------------------------------------
-- Table `Pelajaran`
-- -----------------------------------------------------
CREATE TABLE Pelajaran (
idPelajaran VARCHAR(3) NOT NULL,
NamaPelajaran VARCHAR(45) NULL,
Keterangan VARCHAR(50) NULL,
Constraint PK_Pelajaran PRIMARY KEY (idPelajaran))
go
-- -----------------------------------------------------
-- Table `kelas`
-- -----------------------------------------------------
CREATE TABLE kelas (
kodeKelas INT NOT NULL,
NamaKelas VARCHAR(15) NULL,
Constraint PK_kelas PRIMARY KEY (kodeKelas))
go
-- -----------------------------------------------------
-- Table `Peserta`
-- -----------------------------------------------------
CREATE TABLE Peserta (
IdPeserta VARCHAR(3) NOT NULL,
NamaPeserta VARCHAR(45) NULL,
kelas_kodeKelas INT NOT NULL,
Constraint PK_Peserta PRIMARY KEY (IdPeserta)
)
go
create INDEX fk_Perserta_kelas1_idx on Peserta (kelas_kodeKelas ASC)
go
alter table Peserta
add CONSTRAINT fk_Perserta_kelas1 FOREIGN KEY (kelas_kodeKelas)
REFERENCES kelas (kodeKelas)
go
-- -----------------------------------------------------
-- Table `Peserta_has_Pelajaran`
-- -----------------------------------------------------
CREATE TABLE Peserta_has_Pelajaran (
Peserta_IdPeserta VARCHAR(3) NOT NULL,
Pelajaran_idPelajaran VARCHAR(3) NOT NULL,
constraint PK_PHP PRIMARY KEY (Peserta_IdPeserta, Pelajaran_idPelajaran))
go
create INDEX fk_Peserta_has_Pelajaran_Pelajaran1_idx 
   on Peserta_has_Pelajaran (Pelajaran_idPelajaran ASC)
go
create INDEX fk_Peserta_has_Pelajaran_Perserta_idx 
   on Peserta_has_Pelajaran (Peserta_IdPeserta ASC)
go
alter table Peserta_has_Pelajaran
add CONSTRAINT fk_Peserta_has_Pelajaran_Perserta FOREIGN KEY (Peserta_IdPeserta)
REFERENCES Peserta (IdPeserta)
go
alter table Peserta_has_Pelajaran
add CONSTRAINT fk_Peserta_has_Pelajaran_Pelajaran1 
   FOREIGN KEY (Pelajaran_idPelajaran)
REFERENCES Pelajaran (idPelajaran)
go

Isi tabel dengan beberapa baris data :

INSERT INTO Pelajaran VALUES ('001','Matematika', null);
INSERT INTO Pelajaran VALUES ('002', 'Kimia', null);
INSERT INTO Pelajaran VALUES ('003', 'Fisika', null);
INSERT INTO Pelajaran VALUES('004', 'Bhs Arab', null);
INSERT INTO Kelas VALUES ('7', 'VII');
INSERT INTO Kelas VALUES ('8', 'VIII');
INSERT INTO Kelas VALUES ('9', 'IX');
INSERT INTO Peserta VALUES ('001', 'orang1', '7');
INSERT INTO Peserta VALUES ('002', 'orang2', '8');
INSERT INTO Peserta VALUES ('003', 'orang3', '9');
INSERT INTO Peserta VALUES ('004', 'orang4', '7');
INSERT INTO Peserta VALUES ('005', 'orang5', '8');
INSERT INTO Peserta VALUES ('006', 'orang6', '9');
INSERT INTO Peserta VALUES ('007', 'orang7', '9');
INSERT INTO Peserta VALUES ('008', 'orang8', '7');
INSERT INTO Peserta VALUES ('009', 'orang9', '7');
INSERT INTO Peserta_has_Pelajaran VALUES ('001', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('001', '003');
INSERT INTO Peserta_has_Pelajaran VALUES ('002', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('002', '002');
INSERT INTO Peserta_has_Pelajaran VALUES ('003', '003');
INSERT INTO Peserta_has_Pelajaran VALUES ('003', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('004', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('004', '002');
INSERT INTO Peserta_has_Pelajaran VALUES ('004', '003');
INSERT INTO Peserta_has_Pelajaran VALUES ('005', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('006', '003');
INSERT INTO Peserta_has_Pelajaran VALUES ('007', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('007', '003');
INSERT INTO Peserta_has_Pelajaran VALUES ('008', '003');
INSERT INTO Peserta_has_Pelajaran VALUES ('009', '001');
INSERT INTO Peserta_has_Pelajaran VALUES ('009', '002');
INSERT INTO Peserta_has_Pelajaran VALUES ('009', '003');

Sekarang kita mulai membuat query pivot tabel.
Awalnya dibuat dulu variable untuk menampung query pivot, daftar nama kolom pivot :

DECLARE @dinamicPivotQuery as NVARCHAR(MAX);
DECLARE @namaKoloms as NVARCHAR(MAX);
DECLARE @namaKolomsNotNull as NVARCHAR(MAX);

Buat query untuk mengisi nama- nama kolom pivot yang merupakan nama-nama mata pelajaran (Pelajaran.NamaPelajaran) :

Select @namaKoloms = ISNULL(@namaKoloms+', ','') + QUOTENAME(NamaPelajaran), 
   @namaKolomsNotNull = ISNULL(@namaKolomsNotNull+',','')+ 
    'COALESCE('+QUOTENAME(NamaPelajaran)+', 0) as '+ QUOTENAME(NamaPelajaran)
From (Select distinct idPelajaran, NamaPelajaran From Pelajaran) as MataPelajaran;

Sebelum memabangun pivot-nya. Kita buat dulu sub query (k) yang menampilkan nama kelas dan total pesertanya :

SELECT p.kelas_kodeKelas, count(php.Peserta_IdPeserta) as jumPeserta 
FROM peserta p left outer join peserta_has_pelajaran php 
   on p.IdPeserta = php.Peserta_IdPeserta 
Group By p.kelas_kodeKelas

Selanjutnya sub query (p) yang menampilkan kelas, pelajaran dan jumlah pesertanya :

SELECT k.kodeKelas, k.NamaKelas, pj.NamaPelajaran, count(p.IdPeserta) as jum 
FROM kelas k left outer join peserta p on k.kodeKelas = p.kelas_kodeKelas 
left outer join peserta_has_pelajaran php on p.IdPeserta=php.Peserta_IdPeserta
left outer join pelajaran pj on php.Pelajaran_idPelajaran=pj.idPelajaran 
Group By k.kodeKelas, k.NamaKelas, pj.NamaPelajaran

Terakhir adalah menggabungkan sub-sub query menjadi 1 query dan membangun pivot table yang akan disimpan di variabel dinamicPivotQuery yang sudah dibuat sebelumnya :

SET @dinamicPivotQuery=N'Select NamaKelas as Kelas, jumPeserta as TotalPeserta, ' + 
   @namaKolomsNotNull + ' 
   from (
    Select NamaKelas, jumPeserta, NamaPelajaran, jum, kodeKelas 
    From (
    SELECT p.kelas_kodeKelas, count(php.Peserta_IdPeserta) as jumPeserta 
    FROM peserta p left outer join peserta_has_pelajaran php 
         on p.IdPeserta = php.Peserta_IdPeserta 
    group by p.kelas_kodeKelas 
    ) k left outer join (
    SELECT k.kodeKelas, k.NamaKelas, pj.NamaPelajaran, count(p.IdPeserta) as jum 
    FROM kelas k left outer join peserta p on k.kodeKelas = p.kelas_kodeKelas 
    left outer join peserta_has_pelajaran php 
         on p.IdPeserta=php.Peserta_IdPeserta 
    left outer join pelajaran pj on php.Pelajaran_idPelajaran=pj.idPelajaran 
    Group By k.kodeKelas, k.NamaKelas, pj.NamaPelajaran 
    ) p On k.kelas_kodeKelas = p.kodeKelas 
   ) rekap
   PIVOT (SUM(jum) FOR NamaPelajaran IN(' + @namaKoloms + ')) as pvtTable
   Order By kodeKelas'
;

Jika ditampilkan isi dari dinamicPivotQuery adalah :

Select NamaKelas as Kelas, jumPeserta as TotalPeserta, 
COALESCE([Matematika], 0) as [Matematika], COALESCE([Kimia], 0) as [Kimia],
COALESCE([Fisika], 0) as [Fisika],COALESCE([Bhs Arab], 0) as [Bhs Arab] 
from (
 Select NamaKelas, jumPeserta, NamaPelajaran, jum, kodeKelas 
 From (
 SELECT p.kelas_kodeKelas, count(php.Peserta_IdPeserta) as jumPeserta 
 FROM peserta p left outer join peserta_has_pelajaran php 
   on p.IdPeserta = php.Peserta_IdPeserta 
 group by p.kelas_kodeKelas
 ) k left outer join (
 SELECT k.kodeKelas, k.NamaKelas, pj.NamaPelajaran, count(p.IdPeserta) as jum 
 FROM kelas k left outer join peserta p on k.kodeKelas = p.kelas_kodeKelas 
 left outer join peserta_has_pelajaran php on p.IdPeserta=php.Peserta_IdPeserta 
 left outer join pelajaran pj on php.Pelajaran_idPelajaran = pj.idPelajaran 
 Group By k.kodeKelas, k.NamaKelas, pj.NamaPelajaran
 ) p On k.kelas_kodeKelas = p.kodeKelas 
) rekap 
PIVOT (SUM(jum) FOR NamaPelajaran IN([Matematika], [Kimia], [Fisika], [Bhs Arab])) 
as pvtTable 
Order By kodeKelas

Dan untuk mendapatkan hasil query pivot table kita jalankan isi variable dinamicPivotQuery :