Before someone mentions it, I have seen the exact same SQL question on Stack before and from my point of view, that's actually transposing columns to rows. What I'm trying to accomplish is as seen below in the photos, given the top one, I want to create a new table with the previous data that is flipped in this sense.
You can use the conditional aggregation and union all as follows:
select name_new,
max(case when name = 'PersonA' then A end) as PersonA,
max(case when name = 'PersonB' then A end) as PersonB,
max(case when name = 'PersonC' then A end) as PersonC
from
(select name, 'A1' name_new, A1 A from mytable union all
select name, 'A2' name_new, A2 A from mytable union all
select name, 'A3' name_new, A3 A from mytable union all
select name, 'A4' name_new, A4 A from mytable ) t
group by name_new
(select name, 'A1' name_new, -- I have just given alias to A1, A2 ... Also check updated answer, In final select I have given alias name to name_new