Mysql – Transpose MySQL rows as column names – DYNAMIC

database-designMySQLPHP

My goal is to be able to calculate the time difference between the ticket timestamp being created and closed and if reopened Add time from being reopened to closed again etc..
I have an existing mySQL table called ost_ticket_event as follows:

ost_ticket_event TABLE

I need to display this table like below where the state could be dynamic, ie: created, closed, assigned, transfer or reopened.
The state for one ticket_id could be closed/reopened more than once etc.:

ticket_id//////CREATED///////////////CLOSED//////////////REOPENED/////////////CLOSED2//  
1-----------18-01/15 12:00 -------18-01/15 13:00------18-01/15 13:30------18-01/15 14:00   
2-----------19-01/15 10:00--------20-01/15 09:00-------------NULL------------NULL

I tried LEFT JOIN Group CONCAT but cant seem to get it to produce the desired results.

This is my query:

SET group_concat_max_len=20000;

SET @a = 0;
SET @b = 0;
SET @num := 0;
SET @ticket_id := ''; 

SET @line1 =    CONCAT ( 
 'SELECT ', 
 (
  SELECT CONCAT('ticket_id,', GROUP_CONCAT(' state',@a:=@a+1,', timestamp',@a))     
  FROM ost_ticket_event as ts1
  WHERE ticket_id = 
  ( 
   SELECT ticket_id
   FROM ost_ticket_event tm
   GROUP BY ticket_id
   LIMIT 1
  )
 ),
 ' FROM ost_ticket_event' ,
 (
  SELECT CONCAT(' ', REPLACE(REPLACE(GROUP_CONCAT(' LEFT JOIN ( SELECT ticket_id as         ticket_id',@b:=@b+1,'| state as state',@b,'| timestamp as timestamp',@b,' FROM ( SELECT *| @num := IF(@ticket_id = ticket_id| @num + 1| 1) AS row_number| @ticket_id := ticket_id AS dummy FROM ost_ticket_event ORDER BY ticket_id) AS x',@b,' WHERE x',@b,'.row_number = ',@b,') as t',@b,' ON  ticket_id',@b,' = ticket_id'),',',' '),'|',','))      
  FROM ost_ticket_event as ts2
  WHERE ticket_id = 
  ( 
  SELECT ticket_id
      FROM ost_ticket_event tm
   GROUP BY ticket_id
   LIMIT 1
  )
 ),
 ' GROUP BY ticket_id'
);

PREPARE my_query FROM @line1;
EXECUTE my_query;

This is the output:

SQL RESULT

Best Answer

I worked it out, in-case anyone would like to know the following Query did it:

SELECT CONCAT(
'SELECT `ost_ticket_event`.ticket_id', GROUP_CONCAT('
 ,    `t_', REPLACE(state, '`', '``'), '`.timestamp
     AS `', REPLACE(state, '`', '``'), '`'
 SEPARATOR ''),
' FROM `ost_ticket_event` ', GROUP_CONCAT('
 LEFT JOIN `ost_ticket_event`   AS `t_', REPLACE(state, '`', '``'), '`
        ON `ost_ticket_event`. ticket_id = `t_', REPLACE(state, '`', '``'), '`.ticket_id
       AND `t_', REPLACE(state, '`', '``'), '`.state = ', QUOTE(state)
 SEPARATOR ''),
' GROUP BY `ost_ticket_event`.ticket_id'
) INTO @qry FROM (SELECT DISTINCT state FROM `ost_ticket_event`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;
Related Question