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

Use


MySQL


Create a dynamic cross tabulation for a stored procedure

 

 

Today I read about
SQL
Generate
Crosstab
. I have found many examples on the Internet and learned some things. Now I will summarize the following:

 

One of mine
Demand
Table:

 

Drop table if exists
'Xcvrs '. 'demand ';

Create Table
'Xcvrs '. 'demand '(

'Item _ Code' varchar (15) not null,
/*
Product Code
*/

'Week _ Code' varchar (20) not null,
/*
Weekly code
*/

'Dmd _ qty 'int (10) unsigned not null
/*
Required quantity
*/

) Engine = InnoDB
Default charset = utf8;

 

I want to generate
Crosstab
, The vertical axis is
Item_code
, The horizontal axis is
Week_code
, Body is
Dmd_qty
And. Two methods are provided on the Internet:

L



Direct use
SQL
Statement implementation, usually used for fixed horizontal axis columns;

L



The stored procedure is usually used when the horizontal column is not fixed.

 

1.




Direct use
SQL

Statement implementation

Very simple.
Case
Statement:

Select 'item _ Code ',

Sum (Case week_code when 'feb-wk01 'Then dmd_qty else 0
End) as 'feb-wk01 ',

Sum (Case week_code when 'feb-wk02 'Then dmd_qty else 0
End) as 'feb-wk02 ',

Sum (Case week_code when 'feb-wk03 'Then dmd_qty else 0
End) as 'feb-wk03 ',

Sum (Case week_code when 'feb-wk04 'Then dmd_qty else 0
End) as 'feb-wk04'

From 'demand 'group by 'item _ Code'

 

 

2.




Use stored procedures

I checked
N
Many Chinese articles, almost all of which are
SQL
Server
.
MySQL
There are few examples, and they cannot be found to be able to run smoothly. I was not familiar with the stored procedure, and this is even more dizzy.

Helpless evening
Google
Indirectly found two articles, which I have posted on my
Blog
In:

L



The wizard Revisited: Dynamic
Crosstabs using MySQL stored procedures

L



A mail from the Wizard himself

 

I read these two articles and found some clues. Set
Case
Statement generation using Loops
SQL
To obtain the query result.

 

Delimiter $

 

Drop procedure if exists 'xcvrs '. 'xtab02' $

Create procedure 'xcvrs '. 'xtab02 '()

Begin

 

Declare 'xtab _ query'
Varchar (4096)
Default '';

Declare 'done'
Int
Default 0;

Declare 'temp _ week'

Varchar (10 );

 

Declare 'column _ cursor 'cursor

Select distinct 'Week _ Code' from 'demand 'order by 'Week _ Code ';

Declare continue handler for not found set 'done' = 1;

 

 

/*
Create the case phrase via loop */

Open
'Column _ cursor ';

Column_loop: loop

Fetch 'column _ cursor 'into 'temp _ week ';

If 'done' then leave column_loop; end if;

Set 'xtab _ query' = Concat ('xtab _ query ',

', Sum (Case
'Week _ Code' when /'',

Temp_week,

'/' Then
Dmd_qty else 0 end) '',

Temp_week,

''');

End
Loop column_loop;

Close 'column _ cursor ';

 

 

/*
Connect the SQL parts */

Set
'Xtab _ query' = Concat ('select' Item _ Code '',

'Xtab _ query ','
From demand group by 'item _ Code '');

 

/*
Execute crosstab */

Set
@ Xtab_query = 'xtab _ query ';

Prepare 'xtab02' from @ xtab_query;

Execute 'xtab02 ';

Deallocate prepare 'xtab02 ';

 

 

End $

 

Delimiter;

 

Then execute
Call xtab02 ()
To obtain the required results.