Another Dynamic MySQL Cross Tab

crosstab.txt

Microsoft Windows [Version 6.2.9200]
(c) 2012 Microsoft Corporation. All rights reserved.

C:\Users\Nur>d:

D:\>cd xampp

D:\xampp>cd mysql\bin

D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> create table tbl_soal as
    -> select '001' id_soal, 1 no_soal union all
    -> select '002' id_soal, 2 no_soal union all
    -> select '003' id_soal, 3 no_soal union all
    -> select '004' id_soal, 1 no_soal union all
    -> select '005' id_soal, 2 no_soal ;
Query OK, 5 rows affected (0.16 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from tbl_soal ;
+---------+---------+
| id_soal | no_soal |
+---------+---------+
| 001     |       1 |
| 002     |       2 |
| 003     |       3 |
| 004     |       1 |
| 005     |       2 |
+---------+---------+
5 rows in set (0.00 sec)

mysql> create table tbl_jawaban as
    -> select 1 id_jawaban, '001' id_soal, '101' nis, 'a' jawaban, 1 skor union all
    -> select 2 id_jawaban, '001' id_soal, '102' nis, 'a' jawaban, 1 skor union all
    -> select 3 id_jawaban, '001' id_soal, '103' nis, 'b' jawaban, 0 skor union all
    -> select 4 id_jawaban, '001' id_soal, '104' nis, 'a' jawaban, 1 skor union all
    -> select 5 id_jawaban, '001' id_soal, '105' nis, 'a' jawaban, 1 skor union all
    -> select 6 id_jawaban, '002' id_soal, '101' nis, 'b' jawaban, 1 skor union all
    -> select 7 id_jawaban, '002' id_soal, '102' nis, 'b' jawaban, 1 skor union all
    -> select 8 id_jawaban, '002' id_soal, '103' nis, 'b' jawaban, 1 skor union all
    -> select 9 id_jawaban, '002' id_soal, '104' nis, 'a' jawaban, 0 skor union all
    -> select 10 id_jawaban, '002' id_soal, '105' nis, 'c' jawaban, 0 skor ;
Query OK, 10 rows affected (0.14 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from tbl_jawaban ;
+------------+---------+-----+---------+------+
| id_jawaban | id_soal | nis | jawaban | skor |
+------------+---------+-----+---------+------+
|          1 | 001     | 101 | a       |    1 |
|          2 | 001     | 102 | a       |    1 |
|          3 | 001     | 103 | b       |    0 |
|          4 | 001     | 104 | a       |    1 |
|          5 | 001     | 105 | a       |    1 |
|          6 | 002     | 101 | b       |    1 |
|          7 | 002     | 102 | b       |    1 |
|          8 | 002     | 103 | b       |    1 |
|          9 | 002     | 104 | a       |    0 |
|         10 | 002     | 105 | c       |    0 |
+------------+---------+-----+---------+------+
10 rows in set (0.00 sec)

mysql> select distinct a.id_soal, a.no_soal
    -> from tbl_soal a
    -> join tbl_jawaban b on a.id_soal=b.id_soal ;
+---------+---------+
| id_soal | no_soal |
+---------+---------+
| 001     |       1 |
| 002     |       2 |
+---------+---------+
2 rows in set (0.00 sec)

mysql> delimiter $$
mysql> CREATE PROCEDURE `test`.`sp_mycrosstab`()
    -> BEGIN
    ->  -- declare variables
    ->  declare prepsql varchar(1000);
    ->  declare no_more_rows boolean default false;
    ->  declare soal_id_val varchar(20);
    ->  declare soal_no_val integer;
    ->  declare soal_cur cursor for
    ->          select distinct a.id_soal, a.no_soal
    ->          from tbl_soal a
    ->          join tbl_jawaban b on a.id_soal=b.id_soal ;
    ->  declare continue handler for not found
    ->          set no_more_rows = true;
    ->
    ->  -- start generating sql
    ->  set prepsql = 'select nis';
    ->  open soal_cur;
    ->  the_loop: loop
    ->          -- fetch data
    ->          fetch soal_cur
    ->          into soal_id_val, soal_no_val;
    ->          -- break out loop
    ->          if no_more_rows then
    ->                  close soal_cur;
    ->                  leave the_loop;
    ->          end if;
    ->          -- generate sql crosstab
    ->          set prepsql = concat(prepsql
    ->                  , ', sum(case when a.id_soal=\'', soal_id_val
    ->                  , '\' then ifnull(skor,0) else 0 end) "no_soal_'
    ->                  , soal_no_val , '"');
    ->  end loop the_loop;
    ->  -- continue generate sql crosstab 
    ->  set prepsql = concat(prepsql
    ->          , ', sum(ifnull(skor,0)) total
    '>             from tbl_soal a
    '>             join tbl_jawaban b on a.id_soal=b.id_soal
    '>             group by nis' );
    ->
    ->  -- 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 sp_mycrosstab ;
+-----+-----------+-----------+-------+
| nis | no_soal_1 | no_soal_2 | total |
+-----+-----------+-----------+-------+
| 101 |         1 |         1 |     2 |
| 102 |         1 |         1 |     2 |
| 103 |         0 |         1 |     1 |
| 104 |         1 |         0 |     1 |
| 105 |         1 |         0 |     1 |
+-----+-----------+-----------+-------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>