Put each row obtained in independent columns. MySQL

Question

Put each row obtained in independent columns. MySQL

Navigation

#1 by (1 votes)

1

I make this query:

SELECT idFormacion FROM alumnos_formacion

And I get this:

|idFormacion|
-------------
      6
      9
      12

My question is whether a query can be made to put each row obtained in independent columns.

|idFormacion1| |idFormacion2| |idFormacion3|
-------------  -------------  ------------- 
      6              9              12

I do not know if this will be possible.

mysql sql

asked by Mario Guiber 11.06.2018 в 12:49

source

1 answer

Issues when compiling on Android Compare two split strings of a FILE file

score 1 · Accepted Answer

This kind of ways of showing the data I understand that it is much better to solve it in the data view and not in the SQL logic, the comment of @A. Cedano is a good starting point. However, there may be cases in which it is mandatory to resolve it from the consultation itself. One way to do it would be the following:

SELECT MAX(CASE WHEN nr=1 THEN IdFormacion ELSE NULL END) 'IdFormacion1',
       MAX(CASE WHEN nr=2 THEN IdFormacion ELSE NULL END) 'IdFormacion2',
       MAX(CASE WHEN nr=3 THEN IdFormacion ELSE NULL END) 'IdFormacion3'
    FROM (SELECT t.IdFormacion, 
                 @rownum := @rownum + 1 AS nr
                 FROM alumnos_formacion t, 
                 (SELECT @rownum := 0) r
          ) f;

Let's see:

The initial subquery, generates a numbered list of each of the IdFormacion of alumnos_formacion . This query makes sense if we only have different training, that is, if as I suppose this table also has a id student, I imagine that the query should be done by alumno .
Then using the previous numerator, we simply place with the aggregation function MAX and% CASE each of the formaciones in the indicated column.

Important:

It is essential to establish the maximum possible formations, if there is no maximum, unfortunately you will have to establish one, since an SQL query can not grow indefinitely horizontally (there are limits imposed by the engine)