mySQL create columns from rows

Question

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.

Yes it is. But please explain a bit better what you want and what you've tried — lumio, Jul 12 '17 at 12:46
There are many MySQL pivot table questions on SO, try searching. — Usagi Miyamoto, Jul 12 '17 at 12:48
Can you add the row (4, 4, 33, 44) to the sample data, and adjust the result. — jarlh, Jul 12 '17 at 12:48

NikNik · Answer 1 · 2017-07-12T13:41:10.653

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

How to use GROUP BY to concatenate strings in MySQL?

edited Jul 12 '17 at 13:41

answered Jul 12 '17 at 12:49

NikNik

2,191
2
15
34

score 0 · Accepted Answer · answered Jul 12 '17 at 12:50

0

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

answered Jul 12 '17 at 12:50

Esteban P.

2,789
2
27
43

this is usefull, not very dinamic though. What's this concept of using t1, t2 and t3 called? i'll look for it to study it a bit more – ndelucca Jul 12 '17 at 13:24
t1, t2, t3 are just aliases, you could call them however you want – Esteban P. Jul 12 '17 at 13:41
i see now, you are calling the same table and treating it as if it was a different one and joining only the data required... now can this aproach be modified to be dinamic, so if the number of inputs varies, the query remains the same (and don't have to add the items to select+join for each input)? – ndelucca Jul 12 '17 at 13:53
@ndelucca of course you can create this statement dynamicly, but that was not the question you asked – Esteban P. Jul 12 '17 at 14:56
i made a mess of this question... accepted this answer because it works for the case, and created a new question in regards of the dynamic part. – ndelucca Jul 12 '17 at 20:41

score 0 · Answer 3 · answered Jul 12 '17 at 13:13

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

mySQL create columns from rows

3 Answers3

Linked