mysql 用行实现动态列,MySQL选择动态行值作为列名

This is my given table:

+---------+------------+-------------------+--------------------------+---------------+---------------+

| exec_id | project_id | flow_id | job_id | start_time | end_time |

+---------+------------+-------------------+--------------------------+---------------+---------------+

| 10919 | 16 | my_flow_cleanup | init | 1408480308611 | 1408480308686 |

| 10919 | 16 | my_flow_cleanup | job_id_1 | 1408480309212 | 1408480309426 |

| 10919 | 16 | my_flow_cleanup | job_id_2 | 1408480308721 | 1408480308776 |

| 10919 | 16 | my_flow_cleanup | job_id_3 | 1408480308827 | 1408480309171 |

+---------+------------+-------------------+--------------------------+---------------+---------------+

And I want to achieve a select like this:

exec_id init_start init_end job_id_1_start job_id_1_end job_id_2_start job_id_2_end job_id_3_start job_id_3_end

10919 1408480308611 1408480308686 1408480309212 1408480309426 1408480308721 1408480308776 1408480308827 1408480309171

I have spent more than 2 days on this, with very little success. This question helped me, but not completely. As you can see, the other question deals with taking 2 columns and converting them to 2 rows. But I have to take 3 columns and convert them into 2 rows.

Can anyone help me with this? Is it even possible in MySQL?

EDIT 1

Thanks Khalid Junaid for the answer, it solved my problem. I have to make one more modification. I have to order the selected columns by start_time in the ascending order.

E.g.:

+---------+------------+-------------------+----------+---------------+---------------+

| exec_id | project_id | flow_id | job_id | start_time | end_time |

+---------+------------+-------------------+----------+---------------+---------------+

| 10919 | 16 | my_flow_cleanup | init | 10 | 15 |

| 10919 | 16 | my_flow_cleanup | job_id_1 | 30 | 40 |

| 10919 | 16 | my_flow_cleanup | job_id_2 | 40 | 50 |

| 10919 | 16 | my_flow_cleanup | job_id_3 | 20 | 25 |

+---------+------------+-------------------+----------+---------------+---------------+

Current query will return:

exec_id init_start init_end job_id_1_start job_id_1_end job_id_2_start job_id_2_end job_id_3_start job_id_3_end

10919 10 15 30 40 40 50 20 25

I need:

exec_id init_start init_end job_id_3_start job_id_3_end job_id_1_start job_id_1_end job_id_2_start job_id_2_end

10919 10 15 20 25 30 40 40 50

Please notice that the order of columns is now changed, according to start_time.

I tried to do this with temporary tables and views, I thought it would be very easy. Unfortunately, I don't have create table/create view permissions. Is there a way to achieve this without temp table and views?

解决方案

As from reference question's approach of using group_concat you can do so,but note one thing as your job ids increases per exec_id group then group_concat approach will not be optimal due to its default length of 1024 characters to concatenate and for your dynamic columns this will surely cross that limit,but this limit can be increased as mentioned in documentation

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT

CONCAT('MAX(CASE WHEN job_id = ''',

job_id,

''' THEN start_time END) `',

job_id,

'_start`',

',',

'MAX(CASE WHEN job_id = ''',

job_id,

''' THEN end_time END) `',

job_id,

'_end`'

)

)

INTO @sql

FROM t;

SET @sql = CONCAT('SELECT exec_id, ', @sql, '

FROM t

GROUP BY exec_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;