How to convert search result in mysql into columns

Question

How to convert search result in mysql into columns

Navigation

#1 by (1 votes)

1

Good evening guys, I do not know if anyone has gone through this, but I need to convert the result of a mysql search into columns, not to change the rows through the columns, but to make some results become the titles of column.

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

It should look 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

These dates are dynamic, stored in another table in the database.

I do not know if it is possible to do this type of query, but I thank you for all the help.

mysql sql sql-select

asked by anonymous 14.06.2016 / 23:25

1 answer

Insert footnote in Legend in Latex .htaccess with friendly URL that allows use of the GET method

score 1 · Answer 1

Yes, you can 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, which 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

See, I indicated in the query what dates would be transformed into columns . With each new date, a new column would be needed, that is, a change in the query.

You could even write a program / script that would manage this for you and would result in the query being executed at any given time.

To test and make modifications you can access here: link

I hope I have helped.