How to convert mysql search result into columns

Question

Good evening guys, I do not know if someone has been through this, but I need to convert the result of a search in mysql in columns, is not to exchange the rows for the columns, but rather, make some results become the column titles.

Being:

NOME        DATA
fulano      2016-01-10
fulano      2016-02-15
fulano      2016-03-10
beltrano    2016-01-10
ciclano     2016-02-15
ciclano     2016-03-10
beltrano    2016-04-10

Should stay like this:

NOME        2016-01-10         2016-02-15         2016-03-10       2016-04-10
fulano           X                  X                  X
beltrano         X                                                      X
ciclano                             X                  X

Since these dates are dynamic, stored in another table in the database.

I do not know if it is possible to do this kind of query, but I appreciate the help of all.

Answer 1

Yes, it is possible to do what you want with a query, but we have some limitations.

For example, you would have to build the query already knowing how many columns you would have resulted, that would be the days of your table.

An example:

SELECT resultado.nome,
       COALESCE(MAX(resultado.dia_20160110), '') AS '2016-01-10',
       COALESCE(MAX(resultado.dia_20160215), '') AS '2016-02-15',
       COALESCE(MAX(resultado.dia_20160310), '') AS '2016-03-10',
       COALESCE(MAX(resultado.dia_20160410), '') AS '2016-04-10'
  FROM (  SELECT t.nome,
                 CASE WHEN t.dataaula =  '2016-01-10' THEN 'X' ELSE NULL END AS dia_20160110,
                 CASE WHEN t.dataaula =  '2016-02-15' THEN 'X' ELSE NULL END AS dia_20160215,
                 CASE WHEN t.dataaula =  '2016-03-10' THEN 'X' ELSE NULL END AS dia_20160310,
                 CASE WHEN t.dataaula =  '2016-04-10' THEN 'X' ELSE NULL END AS dia_20160410
            FROM teste t  ) AS resultado
 GROUP BY resultado.nome     

Look at that, i indicated in the query which dates would be transformed into columns. At each new date, a new column would be required, ie a change in the query.

You could even write a program/script that manages this for you and results in the query that would be executed at a given time.

To test and make modifications you can access here: http://sqlfiddle.com/#! 9/f55b41/10

I hope I’ve helped.