Penggunaan GROUP_CONCAT() pada MYSQL Untuk Membuat Tabel Pivot

GROUP_CONCAT() berfungsi untuk menggabungkan nilai dari beberapa kolom ke dalam suatu string. GROUP_CONCAT mempunyai batasan ukuran data yang akan di tampung pad suatu variabel, yaitu hanya mampu menampung 1024 B atau 1KB maka dari itu bisa di lakukan dengan penyetingan menggunakan.

SET GLOBAL | SESSION group_concat_max_len = VALUE

Misal :

SET SESSION group_concat_max_len = 100000;

GROUP_CONCAT sangat berguna jika kita akan menampilkan data dalam bentuk pivot tabel secara dynamis, yang artinya ukuran data pada variabel akan selalu berubah. Pada contoh kasus berikut adalah perbandingan pembuatan tabel pivot untuk menampilkan dafta schedule karyawan group_concat_max_len yang belum di setting (default) dan sudah di setting

A. group_concat_max_len masih default (1024 Bytes)

1. Membuat Variabel

SET @sch := NULL;

2. Membuat query looping dan memasukannya kedalam variabel @sch

SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
                'MAX(IF (SCT_SIMBOL = ''',
                `SCT_SIMBOL`,
                ''', SCT_JAMIN, NULL)) AS `',
                `SCT_SIMBOL`,'`'
            )  ORDER BY SCT_ID ASC SEPARATOR ',\n'
  ) INTO @sch FROM tbl_schtime;

3. Menampilkan data yang sudah di tampung oleh variabel @sch

SELECT @sch;

Query yang dihasilkan pada baris terakhir akan terpotong, karena jumlah data yang sebenarnya lebih dari 1024 Bytes atau 1KB

MAX(IF (SCT_SIMBOL = 'M', SCT_JAMIN, NULL)) AS `M`,
MAX(IF (SCT_SIMBOL = 'TST', SCT_JAMIN, NULL)) AS `TST`,
MAX(IF (SCT_SIMBOL = 'A', SCT_JAMIN, NULL)) AS `A`,
MAX(IF (SCT_SIMBOL = 'OFF', SCT_JAMIN, NULL)) AS `OFF`,
MAX(IF (SCT_SIMBOL = 'N', SCT_JAMIN, NULL)) AS `N`,
MAX(IF (SCT_SIMBOL = 'P', SCT_JAMIN, NULL)) AS `P`,
MAX(IF (SCT_SIMBOL = 'N2', SCT_JAMIN, NULL)) AS `N2`,
MAX(IF (SCT_SIMBOL = 'SP', SCT_JAMIN, NULL)) AS `SP`,
MAX(IF (SCT_SIMBOL = 'A2', SCT_JAMIN, NULL)) AS `A2`,
MAX(IF (SCT_SIMBOL = 'A1', SCT_JAMIN, NULL)) AS `A1`,
MAX(IF (SCT_SIMBOL = 'N1', SCT_JAMIN, NULL)) AS `N1`,
MAX(IF (SCT_SIMBOL = 'P2', SCT_JAMIN, NULL)) AS `P2`,
MAX(IF (SCT_SIMBOL = 'MD', SCT_JAMIN, NULL)) AS `MD`,
MAX(IF (SCT_SIMBOL = 'N3', SCT_JAMIN, NULL)) AS `N3`,
MAX(IF (SCT_SIMBOL = 'N4', SCT_JAMIN, NULL)) AS `N4`,
MAX(IF (SCT_SIMBOL = 'S', SCT_JAMIN, NULL)) AS `S`,
MAX(IF (SCT_SIMBOL = 'S1', SCT_JAMIN, NULL)) AS `S1`,
MAX(IF (SCT_SIMBOL = 'BB',

B. group_concat_max_len dengan ukuran yang sudah ditentukan

1. Set Ukuran

SET SESSION group_concat_max_len = 100000;

2. Membuat Variabel

SET @sch := NULL;

3. Membuat query looping dan memasukannya kedalam variabel @sch

SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
                'MAX(IF (SCT_SIMBOL = ''',
                `SCT_SIMBOL`,
                ''', SCT_JAMIN, NULL)) AS `',
                `SCT_SIMBOL`,
                '`'
            )  ORDER BY SCT_ID ASC SEPARATOR ',\n'
  ) INTO @sch FROM tbl_schtime;

4. Menampilkan data yang sudah di tampung oleh variabel @sch

SELECT @sch;

Query yang dihasilkan sesuai keinginan, karena jumlah data yang dapat di tampung secara keseluruhan oleh variabel @sch;

MAX(IF (SCT_SIMBOL = 'M', SCT_JAMIN, NULL)) AS `M`,
MAX(IF (SCT_SIMBOL = 'TST', SCT_JAMIN, NULL)) AS `TST`,
MAX(IF (SCT_SIMBOL = 'A', SCT_JAMIN, NULL)) AS `A`,
MAX(IF (SCT_SIMBOL = 'OFF', SCT_JAMIN, NULL)) AS `OFF`,
MAX(IF (SCT_SIMBOL = 'N', SCT_JAMIN, NULL)) AS `N`,
MAX(IF (SCT_SIMBOL = 'P', SCT_JAMIN, NULL)) AS `P`,
MAX(IF (SCT_SIMBOL = 'N2', SCT_JAMIN, NULL)) AS `N2`,
MAX(IF (SCT_SIMBOL = 'SP', SCT_JAMIN, NULL)) AS `SP`,
MAX(IF (SCT_SIMBOL = 'A2', SCT_JAMIN, NULL)) AS `A2`,
MAX(IF (SCT_SIMBOL = 'A1', SCT_JAMIN, NULL)) AS `A1`,
MAX(IF (SCT_SIMBOL = 'N1', SCT_JAMIN, NULL)) AS `N1`,
MAX(IF (SCT_SIMBOL = 'P2', SCT_JAMIN, NULL)) AS `P2`,
MAX(IF (SCT_SIMBOL = 'MD', SCT_JAMIN, NULL)) AS `MD`,
MAX(IF (SCT_SIMBOL = 'N3', SCT_JAMIN, NULL)) AS `N3`,
MAX(IF (SCT_SIMBOL = 'N4', SCT_JAMIN, NULL)) AS `N4`,
MAX(IF (SCT_SIMBOL = 'S', SCT_JAMIN, NULL)) AS `S`,
MAX(IF (SCT_SIMBOL = 'S1', SCT_JAMIN, NULL)) AS `S1`,
MAX(IF (SCT_SIMBOL = 'BB', SCT_JAMIN, NULL)) AS `BB`,
MAX(IF (SCT_SIMBOL = 'QHH', SCT_JAMIN, NULL)) AS `QHH`,
MAX(IF (SCT_SIMBOL = 'FRE', SCT_JAMIN, NULL)) AS `FRE`