mysqlsqlunionunpivotlateral-join

How can I transpose the row of a table as well as the column names in mysql?

I have a table which has only one row like below, enter image description here

Here I want to convert the column names to one column and the row into another column, with the column names converted to some predefined numbers as below (A->1E9, B->1E8, ... E->1E5), enter image description here

I can't use pivot commands or else. Is there any good way to do this transform? Thanks!!

Solution

  • Use UNION

    SELECT 1e9 AS divisor, A AS value
FROM yourTable
UNION
SELECT 1e8, B
FROM yourTable
UNION
SELECT 1e7, C
FROM yourTable
...