0

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. enter image description here

enter image description here

Lamtheram
  • 89
  • 1
  • 7

1 Answers1

2

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

SQLFiddle

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • what does name_new represent? – Lamtheram Dec 30 '20 at 10:25
  • see in the sub-query --> `(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` – Popeye Dec 30 '20 at 10:27
  • what have i not understood http://sqlfiddle.com/#!9/997790/4 – Lamtheram Dec 30 '20 at 10:31
  • Ok, alias used in the GROUP BY was not correct. I have updated the answer with SQLFiddle' – Popeye Dec 30 '20 at 10:34
  • one last thing if i was to create a new table with this new format, would I have to go about it manually or? I'm attempting to print all columns that are null for let's say Person A, which is why I was trying to manipulate the table and pass a where clause for null at column Person A – Lamtheram Dec 30 '20 at 11:08
  • the current syntax prohibits me from adding a where clause to your statement, and if there was a way to embed yours into another one which had select all from (your select) where Person A is null – Lamtheram Dec 30 '20 at 11:10
  • You can use `CREATE TABLE AS – Popeye Dec 30 '20 at 11:10
  • To apply WHERE clause use `SELECT * FROM () T WHERE ..` – Popeye Dec 30 '20 at 11:11
  • its seems to be haulting at the group by name_... http://sqlfiddle.com/#!9/997790/16 – Lamtheram Dec 30 '20 at 11:17
  • @ Popeye, thanks for all the help, but I am asking once more if your familiar with MySQL executed in python, then you'll see I've a new unfortunate issue. https://stackoverflow.com/questions/65507118/values-not-being-defined-when-outputted-in-python-mysql if you knew anything in regards, would be much appreciated – Lamtheram Dec 30 '20 at 13:06
  • Check the answer in you other question. – Popeye Dec 30 '20 at 13:31