I have following tables in PostgreSQL 11
name a a_code b b_code
4-aminopyridine aminopyridine N07XX (null) (null)
abacavir sulfate abacavir sulfate J05AF abacavir sulfate J05AF Nucleoside and nucleotide reverse transcriptase inhibitors
I would like to transform the table in such a way that col a and b makes a separate column 'name1' and a_code and b_code as a separate column 'code' and a column 'source' takes values of column name either a or b.
The desired output is:
name name1 source code
4-aminopyridine aminopyridine a N07XX
4-aminopyridine null b null
abacavir sulfate abacavir sulfate a J05AF
abacavir sulfate abacavir sulfate b J05AF Nucleoside and nucleotide reverse transcriptase inhibitors
I am trying following query:
select distinct name,
name1,
source,
code
from table
cross join lateral (
values('a', a), ('b', b)
)as u(source, name1)
cross join lateral (
values('a_code', a_code), ('b_code', b_code)
)as v(code)