Convert From Columns to Rows in phpmyadmin

Question

I have a database in which i need to convert the columns as rows.

+--------+------+-----+-----+
| ID     | Name |  A  |  B  |
+--------+------+-----+-----+
|   1    |  xyz |  3  |  1  |
|   2    |  abc |  4  |  6  |
+--------+------+-----+-----+ 

and i need to display like this if incase is it possible to reduce redandancy and make another table to pass the id for that?

+--------+----------+-----------+---------+
| ID     |  Name    |   Type    |  Cost   |
+--------+----------+-----------+---------+
|   1    |    xyz   |    A      |   3     |
|   1    |    xyz   |    B      |   1     |
|   2    |    abc   |    A      |   4     |
|   2    |    abc   |    B      |   6     |
+--------+----------+-----------+---------+

please, give me any solutions for this query....

Thanks in Advance

Answer 1

You want to unpivot the columns to rows. In MySQL, this is typically done with union all:

select id, name, 'A' as type, A as cost from mytable
union all select id, name, 'B', B from mytable

Very recent versions of MySQL support lateral join, which help shortening the syntax a little, and to improve the efficiency of the query (the table is scanned only once):

select t.id, t.name, x.*
from mytable t
cross join lateral (select 'A', a union all select 'B', b) x(type, cost)