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

Question

I have a table which has only one row like below,

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

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

Answer 1

Use UNION

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

Answer 2

Here is one way to unpivot your data using a lateral join:

select x.*
from mytable t 
cross join lateral (
    select t.a, 1e9
    union all t.b, 1e8
    union all t.c, 1e7,
    union all t.d, 1e6
    union all t.e, 1e75
) x(divisor, val)

The lateral join is more efficient than union to solve the question, because it scans the table only once - although if your table has just one row then obviously it won’t make a visible difference.

The feature is available in MySQL starting version 8.0.14.