Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL CrossTab
D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> select * from mydata;
+-------+------------+-------+
| kode | tanggal | nilai |
+-------+------------+-------+
| CN235 | 2012-01-02 | 100 |
| CN235 | 2012-01-05 | 120 |
| CN235 | 2012-01-12 | 140 |
| CN235 | 2012-01-11 | 150 |
| CN236 | 2012-01-11 | 200 |
| CN235 | 2012-01-15 | 160 |
| CN235 | 2012-01-17 | 170 |
| CN235 | 2012-01-16 | 180 |
| CN236 | 2012-01-14 | 200 |
| CN236 | 2012-01-12 | 400 |
| CN236 | 2012-01-15 | 120 |
| CN236 | 2012-01-17 | 400 |
| CN236 | 2012-01-18 | 600 |
| CN236 | 2012-01-05 | 800 |
+-------+------------+-------+
14 rows in set (0.00 sec)
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `test`.`mysp`$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE `test`.`mysp`()
-> BEGIN
-> -- declare variables
-> declare prepsql varchar(1000);
-> declare no_more_rows boolean default false;
-> declare tanggal_val varchar(20);
-> declare tanggal_cur cursor for
-> select distinct tanggal
-> from mydata order by tanggal;
-> declare continue handler for not found
-> set no_more_rows = true;
->
-> -- start generating sql
-> set prepsql = 'select kode';
-> open tanggal_cur;
-> the_loop: loop
-> -- fetch data
-> fetch tanggal_cur
-> into tanggal_val;
-> -- break out loop
-> if no_more_rows then
-> close tanggal_cur;
-> leave the_loop;
-> end if;
-> -- display
-> set prepsql = concat(prepsql,'\n, sum(case when tanggal=\'',tanggal_val,'\' then ifnull(nilai,0) else 0 end) "',tanggal_val,'"');
-> end loop the_loop;
-> set prepsql = concat(prepsql,'\nfrom mydata 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.00 sec)
mysql> delimiter ;
mysql> call mysp;
+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| kode | 2012-01-02 | 2012-01-05 | 2012-01-11 | 2012-01-12 | 2012-01-14 | 2012-01-15 | 2012-01-16 | 2012-01-17 | 2012-01-18 |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| CN235 | 100 | 120 | 150 | 140 | 0 | 160 | 180 | 170 | 0 |
| CN236 | 0 | 800 | 200 | 400 | 200 | 120 | 0 | 400 | 600 |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
mysql>
@hidayat365

This comment has been minimized.

Copy link
Owner Author

@hidayat365 hidayat365 commented May 16, 2012

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

select kode
, sum(case when tanggal='2012-01-02' then ifnull(nilai,0) else 0 end) "2012-01-02"
, sum(case when tanggal='2012-01-05' then ifnull(nilai,0) else 0 end) "2012-01-05"
, sum(case when tanggal='2012-01-11' then ifnull(nilai,0) else 0 end) "2012-01-11"
, sum(case when tanggal='2012-01-12' then ifnull(nilai,0) else 0 end) "2012-01-12"
, sum(case when tanggal='2012-01-14' then ifnull(nilai,0) else 0 end) "2012-01-14"
, sum(case when tanggal='2012-01-15' then ifnull(nilai,0) else 0 end) "2012-01-15"
, sum(case when tanggal='2012-01-16' then ifnull(nilai,0) else 0 end) "2012-01-16"
, sum(case when tanggal='2012-01-17' then ifnull(nilai,0) else 0 end) "2012-01-17"
, sum(case when tanggal='2012-01-18' then ifnull(nilai,0) else 0 end) "2012-01-18"
from mydata group by kode

@hidayat365

This comment has been minimized.

Copy link
Owner Author

@hidayat365 hidayat365 commented Mar 4, 2014

Menambahkan kolom total

select kode
, sum(case when tanggal='2012-01-02' then ifnull(nilai,0) else 0 end) "2012-01-02"
, sum(case when tanggal='2012-01-05' then ifnull(nilai,0) else 0 end) "2012-01-05"
, sum(case when tanggal='2012-01-11' then ifnull(nilai,0) else 0 end) "2012-01-11"
, sum(case when tanggal='2012-01-12' then ifnull(nilai,0) else 0 end) "2012-01-12"
, sum(case when tanggal='2012-01-14' then ifnull(nilai,0) else 0 end) "2012-01-14"
, sum(case when tanggal='2012-01-15' then ifnull(nilai,0) else 0 end) "2012-01-15"
, sum(case when tanggal='2012-01-16' then ifnull(nilai,0) else 0 end) "2012-01-16"
, sum(case when tanggal='2012-01-17' then ifnull(nilai,0) else 0 end) "2012-01-17"
, sum(case when tanggal='2012-01-18' then ifnull(nilai,0) else 0 end) "2012-01-18"
, sum(ifnull(nilai,0)) as Total
from mydata group by kode

@yusupand2879

This comment has been minimized.

Copy link

@yusupand2879 yusupand2879 commented Mar 4, 2020

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