MySQL CrossTab

crosstab.sql

PS C:\Users\hiday> mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.5.7-MariaDB-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test
Database changed

MariaDB [test]> -- First drop sample table if already exists
MariaDB [test]> DROP TABLE if exists transaksi cascade;
Query OK, 0 rows affected (0.009 sec)

MariaDB [test]> -- Create our test table
MariaDB [test]> CREATE TABLE transaksi as
    ->     select 'CN235' kode, '2012-01-02' tanggal, 100 nilai union all
    ->     select 'N2130' kode, '2012-01-02' tanggal, 120 nilai union all
    ->     select 'CN235' kode, '2012-01-11' tanggal, 140 nilai union all
    ->     select 'CN235' kode, '2012-01-11' tanggal, 150 nilai union all
    ->     select 'CN236' kode, '2012-01-11' tanggal, 200 nilai union all
    ->     select 'CN235' kode, '2012-01-15' tanggal, 160 nilai union all
    ->     select 'CN235' kode, '2012-01-17' tanggal, 170 nilai union all
    ->     select 'CN235' kode, '2012-01-17' tanggal, 180 nilai union all
    ->     select 'N2130' kode, '2012-01-15' tanggal, 200 nilai union all
    ->     select 'CN236' kode, '2012-01-11' tanggal, 400 nilai union all
    ->     select 'CN236' kode, '2012-01-15' tanggal, 120 nilai union all
    ->     select 'CN236' kode, '2012-01-17' tanggal, 400 nilai union all
    ->     select 'N2130' kode, '2012-01-17' tanggal, 600 nilai union all
    ->     select 'CN236' kode, '2012-01-02' tanggal, 800 nilai
    -> ;
Query OK, 14 rows affected (0.018 sec)
Records: 14  Duplicates: 0  Warnings: 0

MariaDB [test]> -- First drop if the procedure already exists
MariaDB [test]> DROP PROCEDURE if exists crosstab_transaksi;
Query OK, 0 rows affected (0.007 sec)

MariaDB [test]> -- Now create our stored procedure
MariaDB [test]> DELIMITER $$
MariaDB [test]> CREATE PROCEDURE crosstab_transaksi()
    -> BEGIN
    ->     -- declare variables
    ->     declare prepsql varchar(1000);
    ->     declare summsql varchar(1000);
    ->     declare totalsql varchar(100);
    ->     -- generate summary clause
    ->     select group_concat(' sum(case when tanggal=\'',tanggal,'\' then nilai else 0 end) as "',tanggal,'"\n')
    ->     into summsql
    ->     from ( select distinct tanggal from transaksi order by tanggal ) tx;
    ->     -- generate full sql query
    ->     set totalsql = ', sum(nilai) as total\n';
    ->     set prepsql = concat('select kode,',summsql,totalsql,'from transaksi group by kode');
    ->     -- executing generated query
    ->     set @mysql = prepsql;
    ->     prepare stmt from @mysql;
    ->     execute stmt;
    ->     -- clean up
    ->     drop prepare stmt;
    -> END$$
Query OK, 0 rows affected (0.007 sec)

MariaDB [test]> DELIMITER ;

MariaDB [test]> -- Let see our sample data
MariaDB [test]> SELECT * from transaksi
    -> ORDER BY kode, tanggal;
+-------+------------+-------+
| kode  | tanggal    | nilai |
+-------+------------+-------+
| CN235 | 2012-01-02 |   100 |
| CN235 | 2012-01-11 |   140 |
| CN235 | 2012-01-11 |   150 |
| CN235 | 2012-01-15 |   160 |
| CN235 | 2012-01-17 |   180 |
| CN235 | 2012-01-17 |   170 |
| CN236 | 2012-01-02 |   800 |
| CN236 | 2012-01-11 |   200 |
| CN236 | 2012-01-11 |   400 |
| CN236 | 2012-01-15 |   120 |
| CN236 | 2012-01-17 |   400 |
| N2130 | 2012-01-02 |   120 |
| N2130 | 2012-01-15 |   200 |
| N2130 | 2012-01-17 |   600 |
+-------+------------+-------+
14 rows in set (0.001 sec)

MariaDB [test]> -- And then call our stored procedure to get the summary
MariaDB [test]> CALL crosstab_transaksi;
+-------+------------+------------+------------+------------+-------+
| kode  | 2012-01-02 | 2012-01-11 | 2012-01-15 | 2012-01-17 | total |
+-------+------------+------------+------------+------------+-------+
| CN235 |        100 |        290 |        160 |        350 |   900 |
| CN236 |        800 |        600 |        120 |        400 |  1920 |
| N2130 |        120 |          0 |        200 |        600 |   920 |
+-------+------------+------------+------------+------------+-------+
3 rows in set (0.001 sec)

Query OK, 1 row affected (0.012 sec)

MariaDB [test]>

Stored Procedure crosstab_transaksi tersebut di atas akan men-generate query seperti ini...

select kode
, sum(case when tanggal='2012-01-02' then nilai else 0 end) as "2012-01-02"
, sum(case when tanggal='2012-01-11' then nilai else 0 end) as "2012-01-11"
, sum(case when tanggal='2012-01-15' then nilai else 0 end) as "2012-01-15"
, sum(case when tanggal='2012-01-17' then nilai else 0 end) as "2012-01-17"
, sum(nilai) as total
from transaksi group by kode;

tx for the coding....
i want to ask, how do i make the parameters in the code above?