How to write a MySQL query that will transpose particular row values into new columns?

Question

Here is the desired result:

sr no. | fullname      | email  
----------------------------------------
1.     | demo user     | demo@test.com 
2.     | sagar panchal | sagar@gmail.com

I tried:

SELECT *
FROM `ds_tbl_event_question` q
LEFT JOIN `ds_tbl_event_participant` p ON q.event_id = p.event_id
AND p.question_id = q.event_question_id
LEFT JOIN `ds_events` e ON e.event_id = q.event_id
WHERE (
q.field_type = 'email'
OR q.field_type = 'full_name'
)
AND e.store_code = 'H9OCEN'
LIMIT 0 , 30

This gave output as shown in the following image:

There is nothing that would tie the answers together, so it is not possible to achieve what you want. You would need to store who answered the questions, then it would be possible. — Shadow, Feb 18 '17 at 10:58
@Shadow, all the fields are not fixed, its dynamic generating — SagarPPanchal, Feb 18 '17 at 11:01
No idea what you mean by your comment and how it is relevant — Shadow, Feb 18 '17 at 11:09
@Shadow, actually for all the forms, fullnames and emails are not compulsory and also not fixed, so its not possible to take fix columns, its depends upon user to add fields ex. fullname, email or phone — SagarPPanchal, Feb 18 '17 at 11:12
Please could you tell me what is in `ds_tbl_event_participant`? Or expand the screenshot so it shows all the columns returned? — Steve Chambers, Feb 24 '17 at 11:48
An interesting read: http://stackoverflow.com/q/10925445/2298301. However, for it to work in your case - as indicated in the comments above - there's a need of having a column like `submit_id` or `user_id`, etc. for each _set_ of answers submitted in one go. Eventually, that field will be used to pivot the table data. — Dhruv Saxena, Feb 25 '17 at 19:19
What input do you have? (Hard to guess from the 'desired result'). — Rick James, Feb 27 '17 at 00:33

Stephan Lechner · Accepted Answer · 2017-02-26T21:43:44.367

It seems as if only field created_date ties together the records from which you want to take full_name and email. Given that, you could group by this created_date and use an aggregate function (e.g. min) considering only the answer-values of those records with a particular field_type-value:

select
  min(CASE field_type WHEN 'full_name' THEN answer ELSE NULL END) as 'fullname',
  min(CASE field_type WHEN 'email' THEN answer ELSE NULL END) as 'email'   
FROM `ds_tbl_event_question` q
     LEFT JOIN `ds_tbl_event_participant` p ON q.event_id = p.event_id AND p.question_id = q.event_question_id
     LEFT JOIN `ds_events` e ON e.event_id = q.event_id
WHERE (
      q.field_type = 'email'
   OR q.field_type = 'full_name'
   )
   AND e.store_code = 'H9OCEN'
group by created_date

Note that the aggregate function (here: min) is necessary because a select-clause must not contain other attributes than those listed in a group by clause except when used in an aggregate function.

In above query I tried to adapt your query as little as possible. Actually the query could be further shortened: The left joins actually turn into a join as you use a WHERE-condition on left hand side values; and the condition on the field_type is superfluous as the case-statements do not consider any other values at all:

select
  min(CASE field_type WHEN 'full_name' THEN answer ELSE NULL END) as 'fullname',
  min(CASE field_type WHEN 'email' THEN answer ELSE NULL END) as 'email'   
FROM `ds_tbl_event_question` q
     JOIN `ds_tbl_event_participant` p ON q.event_id = p.event_id AND p.question_id = q.event_question_id
     JOIN `ds_events` e ON e.event_id = q.event_id AND e.store_code = 'H9OCEN'
group by created_date

score 1 · Answer 2 · answered Feb 24 '17 at 12:10

Try something like this:

SELECT full_names.answer AS full_name,
       emails.answer AS email FROM
(SELECT created_date
 FROM ds_tbl_event_question
 GROUP BY created_date) created_dates
JOIN 
(SELECT created_date, answer
 FROM ds_tbl_event_question
 WHERE field_type = 'full_name') full_names
ON full_names.created_date = created_dates.created_date
JOIN 
(SELECT created_date, answer
 FROM ds_tbl_event_question
 WHERE field_type = 'email') emails
ON emails.created_date = created_dates.created_date

See Rextester demo: http://rextester.com/OBQ69221

score 1 · Answer 3 · answered Feb 24 '17 at 16:17

Seems like the only way to associate is with created date:

Select q.answer as fullname, (select answer from ds_tbl_event_question where field_type = 'email' and created_date = q.created_date) as email from ds_tbl_event_question q where q.field_type = 'full_name';

How to write a MySQL query that will transpose particular row values into new columns?

3 Answers3