General row-to-column stored procedures in MySQL Databases

I found it online and tried it. It is easy to use.

Delimiter $ </P> <p> drop procedure if exists 'test '. 'SP _ row_column_wrap '$ </P> <p> Create definer = 'root' @ 'localhost' procedure 'SP _ row_column_wrap' (in $ Schema_name varchar (64 ), <br/> in $ table_name varchar (64) <br/> begin <br/> declare CNT int (11); <br/> declare $ table_rows int (11 ); <br/> declare I int (11); <br/> declare J int (11); <br/> declare s int (11 ); <br/> declare STR varchar (255); </P> <p> select count (1) From information_schema.columns where table_schema = $ Schema_name and table_name = $ table_name into CNT; </P> <p> select table_rows from information_schema.tables where table_schema = $ Schema_name and table_name = $ table_name into $ table_rows; </P> <p> drop table if exists test. temp; <br/> Create Table if not exists test. temp ('1' varchar (255) not null); </P> <p> set I = 0; <br/> loop1: loop <br/> If I = $ table_rows-1 then <br/> Leave loop1; <br/> end if; <br/> set @ stmt1 = Concat ('alter table test. temp add'', I + 2, ''varchar (255) Not null'); <br/> prepare S1 from @ stmt1; <br/> execute S1; <br/> deallocate prepare S1; <br/> set I = I + 1; <br/> end loop loop1; </P> <p> set S = 0; </P> <p> loop2: loop </P> <p> If S = CNT then <br/> Leave loop2; <br/> end if; <br/> set @ stmt2 = Concat ('select column_name from information_schema.columns where table_schema = "', $ Schema_name, <br/>'" And table_name = "', $ table_name, '"Limit', S, ', 1 into @ temp;'); <br/> prepare S2 from @ stmt2; <br/> execute S2; <br/> deallocate prepare S2; <br/> set J = 0; <br/> set STR = 'select'; </P> <p> loop3: loop <br/> If J = $ table_rows then <br/> Leave loop3; <br/> end if; <br/> set @ stmt3 = Concat ('select ', @ temp, 'from', $ Schema_name ,'. ', $ table_name, 'limit', J,', 1 into @ temp2; '); <br/> prepare S3 from @ stmt3; <br/> execute S3; <br/> set STR = Concat (STR, '"', @ temp2, '"', ','); <br/> deallocate prepare S3; <br/> set J = J + 1; <br/> end loop loop3; <br/> set STR = left (STR, length (STR)-1 ); </P> <p> set @ stmt4 = Concat ('insert into test. temp ', STR,'; '); <br/> prepare S4 from @ stmt4; <br/> execute S4; <br/> deallocate prepare S4; <br/> set S = S + 1; <br/> end loop loop2; <br/> end $ </P> <p> delimiter;

 

Reference resources:

Http://www.javaeye.com/topic/134703

Http://www.ajaxstu.com/MySQLshujuku/233482.html