MySQL CrossTab

crosstab.sql

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>

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

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

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