Select some columns and show column names in MySQL

Question

I have the following table daysTurns:

How can I get a result like this?

date        turn      mail
2017-04-03  turn11    [email protected]
2017-04-03  turn12    [email protected]
2017-04-03  turn13    [email protected]
2017-04-03  turn21    [email protected]
2017-04-03  turn21    [email protected]
2017-04-03  turn21    [email protected]
2017-04-04  turn11    [email protected]
2017-04-04  turn12    [email protected]
2017-04-04  turn13    [email protected]
2017-04-04  turn21    [email protected]
2017-04-04  turn22    [email protected]
2017-04-04  turn23    [email protected]

I assume I would need a query like SELECT * FROM daysTurns.

And this is the expected OUTPUT:

Could you explain in a sentence what you are trying to select? And maybe the structure of the tables you think might need to be involved. — moondaisy, Apr 10 '17 at 21:31
this is a duplicate of http://stackoverflow.com/questions/10513820/convert-sql-columns-to-rows — RoMEoMusTDiE, Apr 10 '17 at 21:43
and of this http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows — RoMEoMusTDiE, Apr 10 '17 at 21:43
@maSTArHiAn this is for SQLServer, my question is for Mysql. — luispa, Apr 11 '17 at 12:46
@moondaisy as you can see on the table, there's 6 turns per day. I need to show a summary of the assigned turns for every user, like the output example. — luispa, Apr 11 '17 at 12:50
true that @LuisGarcía but the solution is no different to mysql for this particular issue — RoMEoMusTDiE, Apr 11 '17 at 20:43

Evgeny · Accepted Answer · 2017-04-10T21:41:14.320

IF you have only 6 columns which look like a 'turn__', then Try to do this:

SELECT date, 'turn11' as turn, mail FROM daysTurns
WHERE turn11 is not null and turn11 <> ''
UNION ALL
SELECT date, 'turn12' as turn, mail FROM daysTurns
WHERE turn12 is not null and turn12 <> ''
UNION ALL
SELECT date, 'turn13' as turn, mail FROM daysTurns
WHERE turn13 is not null and turn13 <> ''
UNION ALL
SELECT date, 'turn21' as turn, mail FROM daysTurns
WHERE turn21 is not null and turn21 <> ''
UNION ALL
SELECT date, 'turn22' as turn, mail FROM daysTurns
WHERE turn22 is not null and turn22 <> ''
UNION ALL
SELECT date, 'turn23' as turn, mail FROM daysTurns
WHERE turn23 is not null and turn23 <> ''

Thank you! Based on your response i can build the correct one: — luispa, Apr 11 '17 at 13:19

score 0 · Answer 2 · answered Apr 18 '17 at 17:15

(Posted on behalf of the OP).

The solution query:

SET @mail = 'the_mail_that_i_need';

SELECT date, 'turn11' as turn, turn11 as mail FROM daysTurns
WHERE turn11 = @mail 
UNION ALL
SELECT date, 'turn12' as turn, turn12 as mail FROM daysTurns
WHERE turn12 = @mail
UNION ALL
SELECT date, 'turn13' as turn, turn13 as mail FROM daysTurns
WHERE turn13 = @mail
UNION ALL
SELECT date, 'turn21' as turn, turn21 as mail FROM daysTurns
WHERE turn21 = @mail
UNION ALL
SELECT date, 'turn22' as turn, turn22 as mail FROM daysTurns
WHERE turn22 = @mail
UNION ALL
SELECT date, 'turn23' as turn, turn23 as mail FROM daysTurns
WHERE turn23 = @mail
ORDER BY date ASC

Select some columns and show column names in MySQL

2 Answers2