Use the MySQL stored procedure to create a dynamic cross tabulation (2)

A mail from the Wizard himself

What an honor: only hours after publishing my blog entry on dynamic crosstabs in MySQL I got an email from Giuseppe Maxia, the Wizard himself.

But not only did the wizard send me a "nice shot! "In his mail, Roland bouman also said" Thank you guys, I learned a lot from those! "Commenting on our articles. That's actually exactly what I 'd have to say to both of them myself.

Giuseppe Maxia, being the real wizard, pointed out in his mail "that you don't need temporary tables to get the column values, but everything can be achieved with normal SQL (albeit not simple ). "According to him there are" at least two methods to overcome the restriction on cursors with prepared queries "which forced me to use a temporary table in my example. although he didn't go into any further details, i'm sure he'll give us some nice insights when he speaks at the opensource database conference next Tuesday in Frankfurt (Be sure to attend if you can, I unfortunately can't) and hopefully at the mysql users Conference 2006.

One solution I thought of when writing the stored procedure this Monday wocould of course be to use group_concat to avoid a temporary table in the first of the two dynamic queries. I chose the cursor loop over group_concat as a loop wocould give us more flexibility to enhance the procedure with more functionality. but after rethinking this I found that you coshould possibly achieve about 90% of what you coshould ever think of WITH NESTED control flow functions inside the group_concat itself.

Create procedure xtab2 ('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-variant with group_concat'
Begin

-- Some heavy double quoting (quoting of already quoted strings) involved here
-- To build the query that builds the list of columns for the crosstab
Set @ column_query: = Concat ('select Concat (group_concat (distinct ',
'/' // Tsum (if ('', 'col _ name','' = ///'/','',
'Col _ name', '',/'//', ', 'col _ value ',
', 0) as'/', '', 'col _ alias ','',/''/'',
'Separator/', // n/'),/', // n/') into @ xtab_query ',
'Col _ from ');

-- Uncomment the following line if you want to see
-- Generated query to assemble the columns
-- Select @ column_query;

Prepare 'column _ query' from @ column_query;
Execute 'column _ query ';
Deallocate prepare 'column _ query ';

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
-- Generated crosstab query for debugging purposes
-- Select @ xtab_query;

-- Execute crosstab
Prepare 'xtab' from @ xtab_query;
Execute 'xtab ';
Deallocate prepare 'xtab ';
End

This version is shorter than the first one and definitely more in the style of how you shoshould normally work with a database: don't loop over rows when you can achieve the same result with a simple query. we'll possibly loose some clarity as soon as we start to add further functionality to the group_concat, but we sure gain speed compared to a loop. it's just that the first of the two dynamic queries now looks like a real mess from all the quoting.