CODEWITHSUNDEEP
mysqlsqlgroup-bypivot
mathcone
mathcone

Reputation: 15

MySQL query to move row data into separate columns?

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?

Upvotes: 1

Views: 37

Answers (1)

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions