Assalamu'alaikum wr wb.

Selamat Pagi....tadi malam saya browsing ada artikel bagus , sayang sekali kalo tidak saya muat di blog. Sebab kalo ada artikel di suatu website bagus kalau tidak di dokumentasikan ke blog atau file pribadi pasti cepet lupa....!
Artikel ini mengenai SQL bagaimana membuat Crosstab SQL. Artikel ini saya copy dari
http://www.futhark.ch/mysql/106.html, berikut artikelnya :

Since I read the excellent article MySQL Wizardry by Giuseppe Maxia some years ago I wanted to implement crosstabs in pure MySQL - and had to wait a long time until 5.0 made it possible. And then of course I didn't find the time. But after Roland Bouman blogged on this same topic yesterday, it was definitely the right moment to try out the real thing: Fully dynamic crosstabs in a pure MySQL stored procedure.



If you're new to crosstabs make sure you read the article mentioned above first. It's likewise instructive and witty. What I do here in pure SQL is actually just a translated and slightly simplified version of Giuseppe Maxia's Perl code.



He was so lucky to know one of those real geeks (just called "The Wizard") who led him through all the tedious steps involved in generating crosstabs. Despite the title, I couldn't revisit the Wizard. Knowing that he possibly only exists in our dreams, I had to work through this all alone... But let's just have a look at the code first, being only a quick suggestion, ready for your improvements.



CREATE PROCEDURE xtab(`col_name` VARCHAR(32), `col_alias` VARCHAR(32),
`col_from` VARCHAR(256), `col_value` VARCHAR(32),
`row_name` VARCHAR(32), `row_from` VARCHAR(256))
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'Generate dynamic crosstabs'
BEGIN
DECLARE `xtab_col_name` VARCHAR(32) DEFAULT '';
DECLARE `xtab_col_alias` VARCHAR(32) DEFAULT '';
DECLARE `xtab_query` VARCHAR(4096) DEFAULT '';
DECLARE `done` BIT(1) DEFAULT 0;

DECLARE `column_cursor` CURSOR FOR
SELECT `temp_col_name`, `temp_col_alias` FROM `xtab_columns`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = 1;

-- We have to use a temporary table here as MySQL doesn't
-- allow us to declare a cursor in prepared statements
DROP TABLE IF EXISTS `xtab_columns`;
SET @column_query := CONCAT('CREATE TEMPORARY TABLE `xtab_columns` ',
'SELECT DISTINCT ',
'`', `col_name`, '` AS `temp_col_name`, ',
'`', `col_alias`, '` AS `temp_col_alias` ',
`col_from`);

PREPARE `column_query` FROM @column_query;
EXECUTE `column_query`;
DEALLOCATE PREPARE `column_query`;

OPEN `column_cursor`;
column_loop: LOOP
FETCH `column_cursor` INTO `xtab_col_name`, `xtab_col_alias`;
IF `done` THEN LEAVE column_loop; END IF;
SET `xtab_query` = CONCAT(`xtab_query`,
'\tSUM(IF(`', `col_name`, '` = \'',
`xtab_col_name`, '\', ',
`col_value`, ', 0)) AS `',
`xtab_col_alias`, '`,\n');
END LOOP column_loop;
CLOSE `column_cursor`;
DROP TABLE IF EXISTS `xtab_columns`;

SET `xtab_query` = CONCAT('SELECT `', `row_name`, '`,\n',
`xtab_query`, '\t',
IF(`col_value` = '1',
'COUNT(*)',
CONCAT('SUM(`', `col_value`, '`)')
),
' AS `total`\n',
`row_from`);

-- Uncomment the following line if you want to see the
-- generated crosstab query for debugging purposes
-- SELECT `xtab_query`;

-- Execute crosstab
SET @xtab_query = `xtab_query`;
PREPARE `xtab` FROM @xtab_query;
EXECUTE `xtab`;
DEALLOCATE PREPARE `xtab`;
END




The procedure uses dynamic SQL via PREPARE/EXECUTE twice: First to build the expressions defining the columns in the crosstab and finally to execute the dynamic crosstab query. It takes six parameters: The first three control the building of the crosstab columns: col_name is the original column on which the crosstab should be done, col_alias could be a different column to be used for the names displayed in the column headers of the crosstab (if you use a JOIN in the next parameter), col_from is the query part starting with FROM to be used to retrieve the crosstab values. The next three parameters control the actual crosstab query: col_value can be set to 1 if you simply want to count in your crosstab, but it can be set to a column name as well to sum up an arbitrary value. row_name is the main crosstab query starting again with FROM, it can as well contain JOINs, WHERE conditions, ORDER BY clauses and usually contains a GROUP BY statement for the column mentioned in row_name.



There were two issues with prepared statements to work around (as of MySQL 5.0.15) that didn't contribute to a cleaner code structure:




* MySQL doesn't yet accept local variables in the prepared statement syntax (see my feature request Bug #13572). So we have to pollute the global variable namespace whenever we use dynamic SQL inside a stored routine.

* I didn't find a way to define a cursor inside a prepared statement: MySQL yields an error on a CREATE PROCEDURE statement if it contains an OPEN statement for a cursor that doesn't have an explicit corresponding DECLARE before it.






To work around the second point it was necessary to use a temporary table. The user invoking the procedure therefore needs the CREATE TEMPORARY TABLE privilege. You could of course change the SQL SECURITY to DEFINER, but I didn't want to do that, as it could give the user the possibility to circumvent access restrictions on tables by just accessing them via a crosstab.



But now let's finally create a tiny test database to try out the xtab stored procedure.



CREATE TABLE employees (
id INT auto_increment PRIMARY KEY,
shop_id INT,
gender ENUM('m', 'f'),
name VARCHAR(32),
salary INT
);

CREATE TABLE shops (
shop_id INT auto_increment PRIMARY KEY,
shop VARCHAR(32)
);

INSERT INTO shops (shop)
VALUES ('Zurich'), ('New York'), ('London');

INSERT INTO employees (shop_id, gender, name, salary)
VALUES
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser', 4700),
(2, 'f', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);




The two examples show that the procedure works for counting as well as for summing up values.



mysql> CALL xtab('gender', 'gender', 'FROM employees', 1, 'shop',
'FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop');
+----------+------+------+-------+
| shop | m | f | total |
+----------+------+------+-------+
| London | 2 | 0 | 2 |
| New York | 0 | 1 | 1 |
| Zurich | 1 | 1 | 2 |
+----------+------+------+-------+
3 rows in set (0.03 sec)

mysql> CALL xtab('gender', 'gender', 'FROM employees', 'salary', 'shop',
'FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop');
+----------+-------+------+-------+
| shop | m | f | total |
+----------+-------+------+-------+
| London | 10300 | 0 | 10300 |
| New York | 0 | 5600 | 5600 |
| Zurich | 4500 | 4700 | 9200 |
+----------+-------+------+-------+
3 rows in set (0.01 sec)



Another Crosstab SP (Kiran Modha at 2006-03-18 10:34:59)


CREATE PROCEDURE `sp_crosstab`(table_name VARCHAR(255), row_field VARCHAR(255), col_field VARCHAR(255), val_field VARCHAR(255), filter VARCHAR(255), agg_func VARCHAR(255))



DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Generate dynamic crosstabs'




begin



declare sql_query varchar(4096);
declare temp_table_sql varchar(4096);
declare colval varchar(32);
declare newcol varchar(32);
declare newline char(1);
declare done bit(1);
declare return_table_query varchar(4096);
declare FldCursor Cursor for select temp_col from xtab_table;
declare continue handler for not found set done=1;




set @newline := char(10);




drop table if exists xtab_table;
set @temp_table_sql := concat('create temporary table xtab_table ',
' select distinct ',
col_field ,
' as temp_col from ' ,
table_name );
prepare column_query from @temp_table_sql;
execute column_query;
deallocate prepare column_query;




set @sql_query := concat('select ', row_field);




open FldCursor;
column_loop: LOOP
fetch FldCursor into colval;
if done then leave column_loop; end if;
set @newcol := concat(' , \n ',
agg_func ,
'(case ' ,
col_field ,
' when ''' ,
colval,
''' then ' ,
val_field , ' else NULL end) as ''' , colval ,''' '
);




set @sql_query = concat(@sql_query, @newcol);
end loop column_loop;
close FldCursor;




set @sql_query = concat(@sql_query, ' from ' , table_name , ' group by ' , row_field );




prepare return_query from @sql_query;
execute return_query;
deallocate prepare return_query;




end;

-----------------------------------------------------------------------------------
Insyaalloh bermanfaat, walaupun tidak sekarang ...............tapi suatu hari insyaalloh berguna.