Question 20206 in Mysql Question posted in
The official MySQL documentation can be found here.

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!!

Tags:

2

Answers


  1. 0

    Use UNION

    SELECT 1e9 AS divisor, A AS value
FROM yourTable
UNION
SELECT 1e8, B
FROM yourTable
UNION
SELECT 1e7, C
FROM yourTable
...
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search