How to query and design tables from upper one into lower one?

Question

Upper table on the picture is my current table and I intend to change it to the lower one

Answer 1

In MySQL, you can unpivot with union all:

(select id, category, 1990 year, `1990` data from mytable where `1990` is not null)
union all
(select id, category, 1991, `1991` from mytable where `1991` is not null)
union all
(select id, category, 1992, `1992` from mytable where `1992` is not null)
union all
(...)
order by id, category, year