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
– lumio, Commented Jul 12, 2017 at 12:46
There are many MySQL pivot table questions on SO, try searching. — Usagi Miyamoto
– Usagi Miyamoto, Commented Jul 12, 2017 at 12:48
Can you add the row (4, 4, 33, 44) to the sample data, and adjust the result. — jarlh
– jarlh, Commented Jul 12, 2017 at 12:48

NikNik · Accepted Answer · 2017-07-12 13:41:10Z

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, 2017 at 13:41

answered Jul 12, 2017 at 12:49

NikNik

2,3012 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Esteban P. · Accepted Answer · 2017-07-12 12:50:21Z

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, 2017 at 12:50

Esteban P.

2,8203 gold badges31 silver badges45 bronze badges

5 Comments

ndelucca Over a year ago

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

Esteban P. Over a year ago

t1, t2, t3 are just aliases, you could call them however you want

ndelucca Over a year ago

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)?

Esteban P. Over a year ago

@ndelucca of course you can create this statement dynamicly, but that was not the question you asked

ndelucca Over a year ago

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.

Passionate Coder · Accepted Answer · 2017-07-12 13:13:21Z

0

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

answered Jul 12, 2017 at 13:13

Passionate Coder

7,3042 gold badges24 silver badges50 bronze badges

Collectives™ on Stack Overflow

mySQL create columns from rows

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related