CODEWITHSUNDEEP
mysqlsqlpivotunpivot
jyaworski
jyaworski

Reputation: 89

MySQL Pivot Field Names to values in row

I'm new to pivoting, so I came here to get some advice on this. I have a table with fields benchmarkname and value. However, another table is populated differently and out of my control: it has each benchmarkname as its own field in the table, with the row value being the value. The layout is below:

Table 1

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| stream    | double      | YES  |     | NULL    |       |
| pisec     | double      | YES  |     | NULL    |       |
| iozws     | double      | YES  |     | NULL    |       |
| iozwb     | double      | YES  |     | NULL    |       |
| iozrs     | double      | YES  |     | NULL    |       |
| iozrb     | double      | YES  |     | NULL    |       |

Table 2

| BenchmarkName | varbinary(43) | YES  |     | NULL    |       |
| Value       | decimal(14,0) | YES  |     | NULL    |       |

My question is: how do I convert the first table to look like second dynamically? I believe the answer lies in a pivot, but I am unsure.

Upvotes: 0

Views: 277

Answers (1)

Taryn
Taryn

Reputation: 247810

I think you want to unpivot the first table. UNPIVOTing takes the data from your columns and converts it into rows. MySQL does not have unpivot so you will have to use a UNION ALL query:

select 'stream' BenchmarkName, stream value
from table1
union all
select 'pisec' BenchmarkName, pisec value
from table1
union all
select 'iozws' BenchmarkName, iozws value
from table1
union all
select 'iozwb' BenchmarkName, iozwb value
from table1
union all
select 'iozrs' BenchmarkName, iozrs value
from table1
union all
select 'iozrb' BenchmarkName, iozrb value
from table1

Upvotes: 1

Related Questions