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> |
This comment has been minimized.
This comment has been minimized.
Menambahkan kolom total select kode |
This comment has been minimized.
This comment has been minimized.
tx for the coding.... |
This comment has been minimized.
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