Split a row based on multiple column values

Question

I have following table in postgres (only 2 rows are shown below).

trial_id    lead_sponsor    lead_sponsor_class  collaborators   collaborators_class 
NCT00004336 NCRR    NIH University of Michigan  Other
NCT00004337 NCRR    NIH null    null

I would like to split each row based on columns lead_sponsor and collaborators and create new column based on them

Expected output is:

trial_id    sponsor_company           sponsor_role        agency
NCT00004336 NCRR                      lead_sponsor        NCRR
NCT00004336 University of Michigan    collaborators       University of Michigan
NCT00004337 NCRR                      lead_sponsor        NCRR

I tried couple of things but I am unable to figure out the solution (I am a newbie in postgres)

SELECT
    *,
    CASE WHEN lead_sponsor is not null THEN lead_sponsor
         WHEN collaborators is not null THEN collaborators
         ELSE ''
    END AS sponsor_company
FROM
    tb ;

Any suggestions here will be really helpful.

Thanks

Answer 1

You can unpivot with a lateral join:

select x.*
from mytable t
cross join lateral (values 
    (trial_id, lead_sponsor,  'lead_sponsor', lead_sponsor),
    (trial_id, collaborators, 'collaborators', collaborators)
) x(trial_id, sponsor_company, sponsor_role, agency)
where x.sponsor_company is not null

Demo on DB Fiddle:

trial_id    | sponsor_company | sponsor_role  | agency    
:---------- | :-------------- | :------------ | :---------
NCT00004336 | NCRR            | lead_sponsor  | NCRR      
NCT00004336 | University      | collaborators | University
NCT00004337 | NCRR            | lead_sponsor  | NCRR