MySQL CrossTab
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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]> |
tx for the coding....
i want to ask, how do i make the parameters in the code above?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Stored Procedure
crosstab_transaksi
tersebut di atas akan men-generate query seperti ini...