Implementation of dynamic row-to-column conversion in MySQL _ MySQL

Implementation of dynamic row-to-column conversion in MySQL: bitsCN.com

The web pages are static and implemented using the case when structure. So I wrote a dynamic one.

SP code:

DELIMITER $

Drop procedure if exists 'test'. 'sp _ row_column_wrap '$

Create definer = 'root' @ 'localhost' PROCEDURE 'sp _ row_column_wrap '(IN $ schema_name varchar (64 ),
IN $ table_name varchar (64 ))
BEGIN
Declare cnt int (11 );
Declare $ table_rows int (11 );
Declare I int (11 );
Declare j int (11 );
Declare s int (11 );
Declare str varchar (255 );
-- Get the column number of the table
Select count (1) from information_schema.columns where
Table_schema = $ schema_name and table_name = $ table_name into cnt;
-- Get the row number of the table
Select table_rows from information_schema.tables where
Table_schema = $ schema_name and table_name = $ table_name into $ table_rows;
-- Check whether the table exists or not
Drop table if exists test. temp;
Create table if not exists test. temp ('1' varchar (255) not null );
-- Loop1 start
Set I = 0;
Loop1: loop
If I = $ table_rows-1 then
Leave loop1;
End if;
Set @ stmt1 = concat ('alter table test. temp add'', I + 2, ''varchar (255) not Null ');
Prepare s1 from @ stmt1;
Execute s1;
Deallocate prepare s1;
Set @ stmt1 = '';
Set I = I + 1;
End loop loop1;
-- Loop1 end;
Set s = 0;
-- Loop2 start
Loop2: loop
-- Leave loop2
If s = cnt then
Leave loop2;
End if;
Set @ stmt2 = concat ('select column_name from information_schema.columns where table_schema = "', $ schema_name,
'"And table_name ="', $ table_name, '"limit', s, ', 1 into @ temp ;');
Prepare s2 from @ stmt2;
Execute s2;
Deallocate prepare s2;
Set @ stmt2 = '';
Set j = 0;
Set str = 'select ';
-- Loop3 start
Loop3: loop
If j = $ table_rows then
Leave loop3;
End if;

BitsCN.com