Create new columns using a column value and fill values from another column

Question

I have the following table in PostgreSQL

id  type  name
146 INN Ofloxacin
146 TRADE_NAME  Ocuflox
146 TRADE_NAME  Ofloxacin
146 TRADE_NAME  Tarivid i.v.
146 TRADE_NAME  Tarivid 400
147 TRADE_NAME  Mictral
147 TRADE_NAME  Neggram
543 INN Amphetamine
543 INN Amfetamine
543 TRADE_NAME  Adzenys xr-odt
543 TRADE_NAME  Adzenys er
543 TRADE_NAME  Dyanavel xr

I would like to create two new columns trade_name and inn and fill their respective value (copying over or concatenate the INN values) from column 'name'. I am expecting the following output

id  trade_name      inn  
146 Ocuflox         Ofloxacin
146 Ofloxacin       Ofloxacin
146 Tarivid i.v.    Ofloxacin
146 Tarivid 400     Ofloxacin
147 Mictral         Ofloxacin
147 Neggram         Ofloxacin
543 Adzenys xr-odt  Amphetamine | Amfetamine
543 Adzenys er      Amphetamine | Amfetamine
543 Dyanavel xr     Amphetamine | Amfetamine

Any help is highly appreciated.

Don't create new columns in that table. Create an altogether new table, if you must. But really this output should be created dynamically in a query only, not stored in this denormalised format - create a view instead. — Bergi
– Bergi, Commented May 9, 2022 at 19:30

JNevill · Accepted Answer · 2022-05-09 20:02:59Z

1

You can get a result set of distinct ids and then join that back to the same table. Once to get trade_names and once to get inn records:

SELECT ids.id,
     tradenames.name as trade_name,
     inns.name as inn
FROM 
    (SELECT DISTINCT id FROM yourtable) as ids
    LEFT OUTER JOIN yourtable as tradenames 
        ON ids.id = tradenames.id
        AND tradenames.type = 'TRADE_NAME'
    LEFT OUTER JOIN yourtable as inns
        ON ids.id = inns.id
        AND inns.type = 'INNS';

You might also be able to pull this off with a pivot, but I think that would be overkill for the two output columns you are after.

answered May 9, 2022 at 20:02

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create new columns using a column value and fill values from another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related