Mysql Create Columns From Rows

From this sort of table: | id | in | value | valueMax | | 1 | 1 | 10 | 25 | | 1 | 2 | 11 | 25 | | 1 | 3 | 12 | 25 | | 2 | 1 | 20 | 35 |

Solution 1:

You can do this:

| id |  value    | valueMax   | 
| 1  |  10,11    |  25,25,25  | 
| 2  |  20,21    |  35,35,35  |

in this way:

SELECT id, GROUP_CONCAT(string SEPARATOR ' ') FROMtableGROUPBY id;

How to use GROUP BY to concatenate strings in MySQL?

Solution 2:

A solution would be a multiple self join:

SELECT t1.id
      ,t1.value     AS value1
      ,t1.valueMax  AS valueMax1
      ,t2.value     AS value2
      ,t2.valueMax  AS valueMax2
      ,t3.value     AS value3
      ,t3.valueMax  AS valueMax3      
  FROM yourtable t1
  LEFT OUTER JOIN yourtable t2
    ON t1.id = t2.id
   AND t2.in = 2
  LEFT OUTER JOIN yourtable t3
    ON t1.id = t3.id    
   AND t2.in = 3WHERE t1.in = 1

Solution 3:

You can also use Mysql Case for getting your desired result

SELECT id, SUM((CASEWHEN `in` =1THENvalueELSE0END)) AS value1,
SUM((CASEWHEN `in` =1THEN valueMAX ELSE0END)) AS valueMAX1,
SUM((CASEWHEN `in` =2THENvalueELSE0END)) AS value2,
SUM((CASEWHEN `in` =2THEN valueMAX ELSE0END)) AS valueMAX2,
SUM((CASEWHEN `in` =3THENvalueELSE0END)) AS value3,
SUM((CASEWHEN `in` =3THEN valueMAX ELSE0END)) AS valueMAX3 FROM `table` groupby id