Result of a MYSQL search in line, not column

It's not a complete solution, but it's already a starting point.

If the number of columns is fixed, you can use IF to calculate:

SELECT
   SUM( IF( motivo='A'), 1, 0 ) AS motivo_a,
   SUM( IF( motivo='B'), 1, 0 ) AS motivo_b,
   SUM( IF( motivo='C'), 1, 0 ) AS motivo_c,
   SUM( IF( motivo='D'), 1, 0 ) AS motivo_d,
   SUM( IF( motivo='E'), 1, 0 ) AS motivo_e
FROM
   motivos

If someone needs the solution for other DBs that do not support the IF function, you can do the same with CASE .

See this post for more details:

  

Flow Control with MySQL

For titles, unfortunately it would also have to be manual. The UNION allows to generate the two lines, one with title, another with count:

SELECT 'Motivo A', 'Motivo B', 'Motivo C'
UNION
SELECT
   SUM( IF( motivo='A'), 1, 0 ),
   SUM( IF( motivo='B'), 1, 0 ),
   SUM( IF( motivo='C'), 1, 0 )

Another thing that might interest you is GROUP_CONCAT , which brings multiple results on one line, but there they would no longer be separate columns. It would be a column with data separated by commas.

You can work the result of your query as a string, so that it can be manipulated without having to presume its existence in a hard-coded way.

Use the aggregate function GROUP_CONCAT to "merge" in a row each column of your query, using the tab you prefer (here I am using ; ):

select 
    GROUP_CONCAT(motivo SEPARATOR ';') as motivos,
    GROUP_CONCAT(numero SEPARATOR ';') as numeros
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp;

 ***
|                                      motivos |   numeros |
|----------------------------------------------|-----------|
| motivo 1;motivo 2;motivo 3;motivo 4;motivo 5 | 3;3;3;3;1 |

Then, for each column to return on one line, concatenate them again, using a line break character \n between them. I use CONCAT_WS to set the tab general at the beginning, making it possible to include more lines in an elegant way, without having to repeat the \n :

select 
  CONCAT_WS (
    '\n',
    GROUP_CONCAT(motivo SEPARATOR ';'),
    GROUP_CONCAT(numero SEPARATOR ';')
   ) as resultado
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp;

 ***
|                                              resultado |
|--------------------------------------------------------|
| motivo 1;motivo 2;motivo 3;motivo 4;motivo 5
3;3;3;3;1 |

Note that the result is just a column of a record, with a line break between the motif group and the group of numbers.

If you want two records, one for each group, I believe a UNION will solve the problem, with a bit of redundancy. It would be nicer if MySQL supported Common Table Expressions, but this will only come in version 8 , so follow the example with UNION:

select 
    GROUP_CONCAT(motivo SEPARATOR ';') as resultado
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp
UNION 
select 
    GROUP_CONCAT(numero SEPARATOR ';') as resultado
from (
  select count(*) as numero, motivo from motivos 
  group by motivo order by numero desc limit 5
) tmp;

 ***
|                                    resultado |
|----------------------------------------------|
| motivo 1;motivo 2;motivo 3;motivo 4;motivo 5 |
|                                    3;3;3;3;1 |

Here is the SQL Fiddle containing the examples above: link

Having said that, just for purposes of completeness, UNION using CTE would look like this:

with consulta as (
  select 
      GROUP_CONCAT(motivo SEPARATOR ';') as motivos,
      GROUP_CONCAT(numero SEPARATOR ';') as numeros
  from (
    select count(*) as numero, motivo from motivos 
    group by motivo order by numero desc limit 5
  ) tmp
)
select motivos from consulta 
union
select numeros from consulta;