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      |
| 2  | 2  |  21   |  35      |
| 2  | 3  |  22   |  35      |

Is it posible to make a select that returns this:

| id | value1 | valueMax1  | value2 | valueMax2  | value3 | valueMax3  |
| 1  |  10    |  25        |  11    |  25        |  12    |  25        |
| 2  |  20    |  35        |  21    |  35        |  22    |  35        |

So far i've tryed solutions with GROUP_CONCAT, or SELECT inside SELECT, but it's not the result i'm looking for. As per comment i'll show next what i want to happen if an IN = 4 is added.

With new data:

| id | in | value | valueMax |
| 1  | 1  |  10   |  25      |
| 1  | 2  |  11   |  25      |
| 1  | 3  |  12   |  25      |
| 1  | 4  |  13   |  35      |
| 2  | 1  |  20   |  35      |
| 2  | 2  |  21   |  35      |
| 2  | 3  |  22   |  35      |
| 2  | 4  |  23   |  35      |

Result of select:

| id | value1 | valueMax1  | value2 | valueMax2  | value3 | valueMax3  | value4 | valueMax4  |
| 1  |  10    |  25        |  11    |  25        |  12    |  25        |  13    |  35        |
| 2  |  20    |  35        |  21    |  35        |  22    |  35        |  23    |  35        |

NOTE: as an aditional feature, is there anyway to actually get a result like this WITHOUT knowing the exact number of IN values? so, the same query would work on a table with 2 posible values of IN, as well as on a table with 5 posible values.

回答1:

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 = 3
 WHERE t1.in = 1 

回答2:

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 ' ') FROM table GROUP BY id;

How to use GROUP BY to concatenate strings in MySQL?

回答3:

You can also use Mysql Case for getting your desired result

SELECT id, SUM((CASE WHEN `in` = 1 THEN value ELSE  0 END)) AS value1,
SUM((CASE WHEN `in` = 1 THEN valueMAX ELSE  0 END)) AS valueMAX1,
SUM((CASE WHEN `in` = 2 THEN value ELSE  0 END)) AS value2,
SUM((CASE WHEN `in` = 2 THEN valueMAX ELSE  0 END)) AS valueMAX2,
SUM((CASE WHEN `in` = 3 THEN value ELSE  0 END)) AS value3,
SUM((CASE WHEN `in` = 3 THEN valueMAX ELSE  0 END)) AS valueMAX3 FROM `table` group by id