Convert wide format data to long format using SQL

Question

I recently started working with SQL for data manipulation. So, forgive me if this is too basic. I have data table1 in the following format.

id  pg_a  pg_b
12   1     0
35   1     1
46   0     1

And I would like to convert this into a long format like shown in the following table.

id  pg  value
12  a     1
12  b     0
35  a     1
35  b     1
46  a     0
46  a     1

I have an sql query using case when but, in no luck. The query only executes the first when in both case statements.

This is the query:

select id,
(case when pg_a is not null then pg_a
     when pg_b is not null then pg_b
     else null 
 end) AS pg,
(case when pg_a is not null then a
      when pg_b is not null then b
      else null
 end) AS value
from table1

What do I need to do differently? Any pointers will be appreciated.

Thank you in advance.

Answer 1

You can use a lateral join:

select t.id, v.*
from t cross join lateral
     (values ('a', pg_a), ('b', pg_b)
     ) v(pg, value);

If you are new to SQL, you might you might be more comfortable with union all:

select id, 'a' as pg, pg_a as value
from t
union all
select id, 'b', pg_b
from t;