There is a table of the form:

id | date | value ----+-------+------- 1 | 21-01 | 10 2 | 21-01 | 20 3 | 22-01 | 10 4 | 22-01 | 20 3 | 23-01 | 10 4 | 23-01 | 20

It is necessary to turn it into a table of the form:

  date | value | date 2 | value2 | date 3 | value3 -------+-------+--------+--------+--------+-------- 21-01 | 10 | 22-01 | 10 | 23-01 | 10 21-01 | 20 | 22-01 | 20 | 23-01 | 20

Is it possible to make a SQL query? If so, how?

  • search by "mysql pivot" - splash58
  • one
    This is definitely a pivot table or pivot. But in your example, the principle by which you group values ​​in rows is not clear. Is the field value ? - artoodetoo

1 answer 1

In general, no, it is impossible. SQL is not intended for "work horizontally". But in a particular case, you can apply the trick : if you know in advance the values ​​distributed horizontally, then the set of columns is predefined.

The trick is to group + a set of conditional functions, one for each calculated value.

Literally the answer as you can get this way:

 SELECT '21-01' as `date`, SUM(IF(`date`='21-01', `value`, NULL)) AS `value`, '22-01' as `date2`, SUM(IF(`date`='22-01', `value`, NULL)) AS `value2`, '23-01' as `date3`, SUM(IF(`date`='23-01', `value`, NULL)) AS `value3` FROM `my_table` GROUP BY `value`

But maybe you need to group by some other field. With the same value of the value * lines look somehow meaningless.