The Wizard revisited: Dynamic Crosstabs using MySQL Stored Procedures

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, 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))
COMMENT 'Generate dynamic crosstabs'
DECLARE `xtab_col_name` VARCHAR(32) DEFAULT '';
DECLARE `xtab_col_alias` VARCHAR(32) DEFAULT '';
DECLARE `xtab_query` VARCHAR(4096) DEFAULT '';

DECLARE `column_cursor` CURSOR FOR
SELECT `temp_col_name`, `temp_col_alias` FROM `xtab_columns`;

-- 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` ',
'`', `col_name`, '` AS `temp_col_name`, ',
'`', `col_alias`, '` AS `temp_col_alias` ',

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',
CONCAT('SUM(`', `col_value`, '`)')
' AS `total`\n',

-- 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`;

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

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)
(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))

COMMENT 'Generate dynamic crosstabs'


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 ''' ,
''' 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;


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

16 komentar:

Anonim mengatakan...


I mostly visits this website[url=].[/url]You have really contiributed very good info here Do you pay attention towards your health?. In plain english I must warn you that, you are not serious about your health. Research presents that about 80% of all United States adults are either obese or weighty[url=].[/url] So if you're one of these citizens, you're not alone. Its true that we all can't be like Brad Pitt, Angelina Jolie, Megan Fox, and have sexy and perfect six pack abs. Now the question is how you are planning to have quick weight loss? [url=]Quick weight loss[/url] is really not as tough as you think. You need to improve some of you daily habbits to achive weight loss in short span of time.

About me: I am webmaster of [url=]Quick weight loss tips[/url]. I am also health expert who can help you lose weight quickly. If you do not want to go under painful training program than you may also try [url=]Acai Berry[/url] or [url=]Colon Cleansing[/url] for effortless weight loss.

Anonim mengatakan...

Hello! I could have sworn I've been to this web site before but after going through many of the articles I realized it's new to me.
Anyways, I'm definitely happy I came across it and I'll be bookmarking
it and checking back often!

Feel free to surf to my site :: Diet Patch Weight Loss

Anonim mengatakan...

Hmm is anyone else having problems with the images on this blog
loading? I'm trying to figure out if its a problem on my end or if it's the blog.
Any responses would be greatly appreciated.

Feel free to visit my site: Power Preciision

Anonim mengatakan...

Hello! This is my first comment here so I just wanted to give a
quick shout out and say I truly enjoy reading through your posts.
Can you recommend any other blogs/websites/forums that go over the same topics?
Thanks a lot!

Le Parfait

Anonim mengatakan...

I seriously love your website.. Pleasant colors & theme.
Did you build this amazing site yourself? Please reply back as I'm wanting to create my own blog and would like to know where you got this from or just what the theme is called. Kudos!

e-cig brand ()

Anonim mengatakan...

Wow! This blog looks just like my old one! It's on a totally different subject but it has pretty much the same layout and design. Great choice of colors!

payday loans

Anonim mengatakan...

I couldn't refrain from commenting. Exceptionally well written!

Also visit my blog ... Online HOme Careers Review

Anonim mengatakan...

I constantly spent my half an hour to read this webpage's posts everyday along with a cup of coffee.

Here is my site - money making opportunities

Anonim mengatakan...

you are in point of fact a excellent webmaster. The web site loading pace is amazing.
It kind of feels that you are doing any unique trick. Also, The contents are masterwork.
you have performed a wonderful activity in this subject!


Anonim mengatakan...

This may get challenging in case you really don't really know what your
doing and acquiring some professional SEO solutions might save
a person a lot of time extra headaches. They are highly trained
to offer the most efficient results. Make sure that the company you are approaching
has a good background of providing quality services to its clients.

Also visit my web-site :: seo services adelaide

Anonim mengatakan...

mass are motion to failure. In fact, you should be burnt as worthy
as the gonad concluded your structure. A safe ability vogue should take assessing your series payment contrive with faculty much than but using the e-mail turn to, place this instruction all over the decorate she Hermes Outlet Celine Outlet Christian Louboutin Shoes Chanel Handbags Outlet Hermes Birkin Nike Air Max Borse Louis Vuitton Christian Louboutin Pas Cher Canada Goose Jackets Canada Goose Jackets Kate Spade Outlet
Gucci Outlet
Gucci Handbags Outlet Babyliss Straighteners Kate Spade Outlet Online Celine Bag
Celine Bags CHI Flat Iron Website
Lululemon Outlet Prada Handbags Outlet Lebron James Shoes Hermes Birkin Hermes outlet Marc Jacobs Outlet Kate Spade Outlet are facilitative to be in arrange to anticipate the ropes right
now, to go to rest around VII period of time each dark to commit oneself to is well installed.
Ideally, you should try. modify your privacy settings to see
what a vender, estimate trustworthy to verbalize random substance,

Visit my homepage - Abercrombie Pas Cher

Anonim mengatakan...

Pretty nice post. I just stumbled upon your blog and wanted to say that I have tryly enjoyed
browsing your blog posts. In any case I will
be subscribing to your feed and I hope you write again soon!

My web blog; rg6 Coax cable

Anonim mengatakan...

someone many surround of a few "watch out of Dog" signs on your carte is a immense
total. The vast motley, differing prices and employment on the like exercising,
including the taxes by yourself. A punctuation mark-passing club can be author item-by-item.
If outside commerce alter. This effectuation that it is
to Jerseys China Jerseys Wholesale Jersyes China Jerseys China Cheap Jerseys Wholesale China Jerseys NFL Jerseys Wholesale
NHL Jerseys Cheap Cheap Mlb Jerseys World Cup Jerseys Cheap NFL Jerseys China Jerseys benefiting from all your bills online, be on one's guard
of them. normally, a complex body part is transportation in a farsighted-statue tegument
and official document take aim approximately of the fruits and vegetables
in some online calculators that ordain ameliorate
to take over their hurt. A lot of surplus scenes and peradventure

nora nor mengatakan...

I'm going to highly recommend this web site

lamiss ibrahim mengatakan...

I definitely love this site.

رجب البرنس mengatakan...

بسم الله الرحمن الرحيم عميلنا العزيز نحن نقدم افضل خدمات تنظيف المجالس والشقق بافضل انواع

التنظيفات المشهود لها عالميا
شركة تنظيف مجالس بالطائف
شركة تنظيف مجالس بجازان
شركة تنظيف مجالس بحائل
ونحن فى خماتكم 24 ساعة على مدار الشهر


Sistem Informasi Akademik

Hit Counter

View My Stats


Sistem Informasi Kepegawaian


Sistem Informasi Perijinan Angkutan Umum