MySql query to dynamically convert rows to columns

DOWNLOADDEMO

In some cases pivoting a table is not desired, instead we need to convert rows to columns and append them to the result set without “grouping” them against a specific value. In some cases we simply need to append a column, or columns, for each row and name the new columns something arbitrary like data1, data2, data3, etc.

For example lets consider the following dataset:

The final result should be something like this:

Convert rows to columns using LEFT JOIN

The main idea is to use multiple LEFT JOINs on the same dataset, for each row we need to convert to column. We are using LEFT JOIN instead of INNER because we want the query to return NULL in some cases.

Of course since we don’t know the number of rows to be converted beforehand we must use prepared statements and some string functions like CONCAT and GROUP_CONCAT in order to create the SQL statement and then execute it.

The query

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;