2

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)
Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
rshar
  • 1,381
  • 10
  • 28

0 Answers0