[MySQL] 3 Langkah membuat Pivot Table (Dynamic Rows to Columns)

Beranda

...

Programmer Forum
[MySQL] 3 Langkah membuat Pivot Table (Dynamic Rows to Columns)

hawkeye22

17-05-2013 15:07

[MySQL] 3 Langkah membuat Pivot Table (Dynamic Rows to Columns)

Sepertinya, sudah banyak yang bertanya tentang Pivot Table dengan istilah yang berbeda-beda..
Saya coba buat sebuah tutorial Khusus berdasarkan masalah di Thread ini supaya untuk selanjutnya, masalah seperti ini tidak berulang-ulang dipertanyakan..
Berdasarkan kasus di atas, diketahui ada sebuah table dengan data nilai

Spoiler for Tabel Nilai:

Code:

id | Nama | Nilai

=========================

1 | Udin | 80

1 | Udin | 40

2 | Joko | 30

2 | Joko | 10

dari table tersebut, hasil yang diinginkan adalah, seperti ini:

Spoiler for HasilYgDiinginkan:

Code:

Udin | Joko

============

 80 | 30

 40 | 10

lalu ketika ditambahkan data baru, misalnya Nama:Soleh, Nilai:40,50,
hasil yang diinginkan adalah, seperti ini:

Spoiler for HasilLainnya:

Code:

Udin | Joko | Soleh

===================

 80 | 30 | 40

 40 | 10 | 50

Sederhanya, untuk membuat pivot table seperti di atas, ada 3 langkah:

Spoiler for 3 Langkah membuat Pivot Table:

tentukan query untuk membuat columnHeader
tentukan queryBuilder untuk membuat query 1 menjadi dinamis
membuat store prosedur untuk menjalankan query 2 dan merangkai pivot table secara dinamis

Jika diperhatikan, Table Nilai di atas memiliki 3 kolom yaitu, id, nama, dan nilai dengan data nilai perorang memiliki 2 nilai berbeda,
karena dalam table ini telah ditentukan masing-masing memiliki 2 nilai, maka perlu tambahan 1 kolom untuk menyamakan jenis nilai, sebut saja kolom order.
Hasilnya, Table Nilai akan menjadi seperti ini.

Spoiler for TableNilaiBaru:

Code:

ID | order | nama | nilai

===============================

1 | 1 | Udin | 80

1 | 2 | Udin | 40

2 | 1 | Joko | 30

2 | 2 | Joko | 10

3 | 1 | Soleh | 40

3 | 2 | Soleh | 50

Dari Table Nilai yg baru, maka dapat diketahui bahwa nilai memiliki 2 jenis, yaitu nilai 1 dan 2 berdasarkan kolom order.

Setelah itu, mari kita mulai menjalankan 3 Langkah Membuat Pivot Table.
1. query untuk membuat columnHeader
Jika kita menginginkan rows berisi `nama`menjadi header, maka cukup kita membuat pengelompokan berdasarkan `nama`,
kira-kira seperti ini cara mengelompokkannya:

Spoiler for columnHeader:

Code:

SELECT

MAX(case when `nama` = 'Udin' then nilai end) AS Udin,

MAX(case when `nama` = 'Joko' then nilai end) AS Joko,

MAX(case when `nama` = 'Soleh' then nilai end) AS Soleh

FROM table_nilai

Hasilnya, seperti DEMO

2. Menentukan queryBuilder untuk membuat query 1 menjadi dinamis
Untuk membangun ratusan/ribuan Column Header, jika kita buat satu persatu seperti Nomor 1 di atas, tentunya akan GEMPORRR DIJAMIN!!!
ini solusinya:

Spoiler for columnHeaderMaker:

Code:

SELECT

 GROUP_CONCAT(DISTINCT

 CONCAT(

 'MAX(case when `nama` = ''',

 `nama`,

 ''' then nilai end) AS ',

 `nama`

 ) ORDER BY id ASC SEPARATOR ',\n'

 )

from table_nilai;

hasilnya, akan seperti DEMO2
bisa kita lihat, kita gunakan GROUP_CONCAT "itu" lebih baik.
saya sampaikan terima kasih kepada keluarga CONCAT..

3. Membuat store prosedur
Akhirnya, kita sampai di penghujung acara emoticon-Big Grin

begini kira2 prosedurnya:

Spoiler for StoreProcedure:

Code:

SET @sql = NULL;

##2

SELECT

 GROUP_CONCAT(DISTINCT

 CONCAT(

###1

 'MAX(case when `nama` = ''',

 `nama`,

 ''' then nilai end) AS ',

 `nama`

 ) ORDER BY id ASC SEPARATOR ',\n'

 ) INTO @sql

from table_nilai;

#3

SET @sql = CONCAT('SELECT `order`, ', @sql, ' from table_nilai GROUP BY `order`');



PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

dan hasilnya bisa dilihat DEMO3

Spoiler for Hasil Akhir:

Code:

hasilnya

 order | Udin | Joko | Soleh

=============================

 1 | 80 | 30 | 40

 2 | 40 | 10 | 50

ok, cukup sekian, semoga bermanfaat,,

Diubah oleh hawkeye22 17-05-2013 15:42

nona212 memberi reputasi

19.2K

Kutip

Balasan

Guest

Tulis komentar menarik atau mention replykgpt untuk ngobrol seru

Mari bergabung, dapatkan informasi dan teman baru!

Programmer Forum

20.2KThread•4.2KAnggota

Terlama

Guest

Tulis komentar menarik atau mention replykgpt untuk ngobrol seru