MySql select dynamic row values as column names

Question

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?

Why do you want separate table for each record ? Instead, why can't you create a new table with generic fields (job_id, init_start, init_end,job_id_start,..) ? — Venkatesh K, Aug 20 '14 at 17:36
@vengets: I am not creating a new table. I have to query this table and append the results to a file — Bhushan, Aug 20 '14 at 17:47

score 2 · Accepted Answer · answered Aug 20 '14 at 18:01

2

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;

`Fiddle Demo`

answered Aug 20 '14 at 18:01

M Khalid Junaid

63,861
10
90
118

I got the above mentioned problem working. Here it is in fiddle: http://sqlfiddle.com/#!2/a88db/5 . but I have to make a small change in the schema, `execution_jobs` and `execution_flows` have few columns with same name (`start_time` and `end_time`) which I can't change. I am trying this http://sqlfiddle.com/#!2/1b8c46/1, but it doesn't work. Can you help me with it? Thanks – Bhushan Aug 21 '14 at 20:41
@Bhushan from your above comment it isn't clear can you ask a new question with all details and also if you want me to look at the new one then provide me the link here – M Khalid Junaid Aug 21 '14 at 20:48
I have added another sub-question. Can you take a look? Thanks. – Bhushan Sep 05 '14 at 02:12
@Bhushan can you ask new question for the above problem with all the information regarding the problem that will be good for me and new visitors too also provide me the link of new question here regards – M Khalid Junaid Sep 05 '14 at 13:14
Here is the new question: http://stackoverflow.com/questions/25690485/mysql-select-dynamic-row-values-as-column-names-in-specific-order – Bhushan Sep 05 '14 at 16:45

score 0 · Answer 2 · answered Aug 20 '14 at 17:52

Yes, it's possible in MySQL, but with any SQL database, you can't make a query that dynamically creates columns from the data values it finds. Columns of a query must be fixed at the time you prepare the query. So you must know the distinct values you want to be columns beforehand. This may require another query. This is true in all SQL databases, not just MySQL.

SELECT exec_id,
 MIN(CASE job_id WHEN 'init' start_time END) AS init_start,
 MIN(CASE job_id WHEN 'init' end_time END) AS init_end,
 MIN(CASE job_id WHEN 'job_id_1' start_time END) AS job_id_1_start,
 MIN(CASE job_id WHEN 'job_id_1' end_time END) AS job_id_1_end
FROM `this_is_my_given_table`
GROUP BY exec_id

This basically hard-codes each distinct job id, but that's necessary.

MySql select dynamic row values as column names

EDIT 1

2 Answers2

`Fiddle Demo`

Linked