MySQL Crosstab Example, Mengubah Row Menjadi Kolom hanya dengan satu perintah SQL saja via @berthojoris

crosstab3.sql

mysql> use test;
Database changed

mysql> ---------------------------------
mysql> -- buat table dan sample datanya
mysql> ---------------------------------
mysql> create table tbl_data as
    -> select '1' ID ,'001' NOPEL, 'INQUIRY' KATEGORI union all
    -> select '2' ID ,'002' NOPEL, 'COMPLAINT' KATEGORI union all
    -> select '3' ID ,'003' NOPEL, 'REQUEST' KATEGORI union all
    -> select '4' ID ,'004' NOPEL, 'INQUIRY' KATEGORI union all
    -> select '5' ID ,'005' NOPEL, 'REQUEST' KATEGORI union all
    -> select '6' ID ,'001' NOPEL, 'COMPLAINT' KATEGORI union all
    -> select '7' ID ,'001' NOPEL, 'REQUEST' KATEGORI union all
    -> select '8' ID ,'003' NOPEL, 'REQUEST' KATEGORI union all
    -> select '9' ID ,'004' NOPEL, 'INQUIRY' KATEGORI ;
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> ---------------------------------
mysql> -- lihat isi sample datanya
mysql> ---------------------------------
mysql> select * from tbl_data;
+----+-------+-----------+
| ID | NOPEL | KATEGORI  |
+----+-------+-----------+
| 1  | 001   | INQUIRY   |
| 2  | 002   | COMPLAINT |
| 3  | 003   | REQUEST   |
| 4  | 004   | INQUIRY   |
| 5  | 005   | REQUEST   |
| 6  | 001   | COMPLAINT |
| 7  | 001   | REQUEST   |
| 8  | 003   | REQUEST   |
| 9  | 004   | INQUIRY   |
+----+-------+-----------+
7 rows in set (0.00 sec)

mysql> ---------------------------------
mysql> -- bikin query-nya
mysql> ---------------------------------
mysql> select nopel
    -> , sum(case when kategori='INQUIRY' then 1 else 0 end) inquiry
    -> , sum(case when kategori='REQUEST' then 1 else 0 end) request
    -> , sum(case when kategori='COMPLAINT' then 1 else 0 end) complaint
    -> , sum(case when kategori='INQUIRY' then 1 else 0 end) 
    -> + sum(case when kategori='REQUEST' then 1 else 0 end) 
    -> + sum(case when kategori='COMPLAINT' then 1 else 0 end) total
    -> from tbl_data
    -> group by nopel ;
+-------+---------+---------+-----------+-------+
| nopel | inquiry | request | complaint | total |
+-------+---------+---------+-----------+-------+
| 001   |       1 |       1 |         1 |     3 |
| 002   |       0 |       0 |         1 |     1 |
| 003   |       0 |       2 |         0 |     2 |
| 004   |       2 |       0 |         0 |     2 |
| 005   |       0 |       1 |         0 |     1 |
+-------+---------+---------+-----------+-------+
5 rows in set (0.00 sec)

mysql> ---------------------------------
mysql> -- YAY!!! I'm genius!
mysql> ---------------------------------
mysql>

RAW query

select nopel
, sum(case when kategori='INQUIRY' then 1 else 0 end) jml_inquiry
, sum(case when kategori='REQUEST' then 1 else 0 end) jml_request
, sum(case when kategori='COMPLAINT' then 1 else 0 end) jml_complaint
, sum(case when action='OCR' then 1 else 0 end) jml_ocr
, sum(case when action='NOCR' then 1 else 0 end) jml_nocr
, group_concat(case when action='OCR' then detail else null end separator ',') list_ocr
, group_concat(case when action='NOCR' then detail else null end separator ',') list_nocr
from tbl_data
group by nopel

Alternate query

select nopel
, sum(case when kategori='INQUIRY' then 1 else 0 end) inquiry
, sum(case when kategori='REQUEST' then 1 else 0 end) request
, sum(case when kategori='COMPLAINT' then 1 else 0 end) complaint
, sum(case when kategori='INQUIRY' then 1 else 0 end) 
+ sum(case when kategori='REQUEST' then 1 else 0 end) 
+ sum(case when kategori='COMPLAINT' then 1 else 0 end) total
from tbl_data
group by nopel ;

bagaimana kalao nama kolomnya dinamis misal ambil dari datanya,