Transpose MySQL rows as column names - DYNAMIC

Question

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

• Colin 't Hart: Please at least show us what you tried.
• DBGUY: My bad ive included it now :)

Answer 1

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;

• Pacerier: Don't just post code alone. Please explain the meat of the answer. The "How it works" and etc.