MySQL Rows to Columns

pivot.sql

-- http://stratosprovatopoulos.com/web-development/mysql/mysql-dynamically-convert-rows-to-columns/

SET group_concat_max_len=20000;

SET @a = 0;
SET @b = 0;
set @num := 0;
SET @regno := ''; 

SET @line1 = 	CONCAT ( 
 'SELECT ', 
 (
  SELECT CONCAT('regno,', GROUP_CONCAT(' s',@a:=@a+1,', m',@a,', rk',@a))    	
  FROM test1 as ts1
  WHERE regno = 
  ( 
   SELECT regno
   FROM test1 tm
   GROUP BY regno
   ORDER BY count(tm.subject) DESC
   LIMIT 1
  )
 ),
 ' FROM test1' ,
 (
  SELECT CONCAT(' ', REPLACE(REPLACE(GROUP_CONCAT('	LEFT JOIN ( SELECT regno as reg',@b:=@b+1,'| subject as s',@b,'| medium as m',@b,'| remark as rk',@b,' FROM ( SELECT *| @num := IF(@regno = regno| @num + 1| 1) AS row_number| @regno := regno AS dummy FROM test1 ORDER BY regno) AS x',@b,' WHERE x',@b,'.row_number = ',@b,') as t',@b,' ON  reg',@b,' = regno'),',',' '),'|',','))    	
  FROM test1 as ts2
  WHERE regno = 
  ( 
   SELECT regno
   FROM test1 tm
   GROUP BY regno
   ORDER BY count(tm.subject) DESC
   LIMIT 1
  )
 ),
 ' GROUP BY regno'
);

PREPARE my_query FROM @line1;
EXECUTE my_query;
SHARE
0
0
0