MySQL query to move row data into separate columns?

Question

Suppose I have a table with a bunch of test data where each row has a device number, test number and the test result.

|--------------------------------|
| device | test_number |  result |
|--------------------------------|
|    1   |      1      |  12.4   |
|--------------------------------|
|    1   |      2      |  13.2   |
|--------------------------------|
|    1   |      3      |  16.0   |
|--------------------------------|
|    1   |      4      |  88.4   |
|--------------------------------|
|    2   |      1      |  10.1   |
|--------------------------------|
|    2   |      2      |  9.3    |
|--------------------------------|
|    2   |      3      |  1.01   |
|--------------------------------|
|    2   |      4      |  3.4    |
|--------------------------------|

I want to output a report spreadsheet. So I need some kind of MySQL select statement that can output a table in this format.

|------------------------------------------|
| test_number | result_dev1 |  result_dev2 |
|------------------------------------------|
|      1      |     12.4    |     10.1     |
|------------------------------------------|
|      2      |     13.2    |      9.3     |
|------------------------------------------|
|      3      |     16.0    |     1.01     |
|------------------------------------------|
|      4      |     88.4    |      3.4     |
|------------------------------------------|

I can't figure out how to do this even though it seems like it should be straight forward. Is there a good trick for grouping row entries into new columns like this?

GMB · Accepted Answer · 2020-10-01 17:14:02Z

1

For a fixed list of devices, you can do conditional aggregation:

select test_number,
    max(case when device = 1 then result end) as result_dev1,
    max(case when device = 2 then result end) as result_dev2
from mytable
group by test_number

answered Oct 1, 2020 at 17:14

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

MySQL query to move row data into separate columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related