CODEWITHSUNDEEP
sqlpostgresql
ThirdGhostHand
ThirdGhostHand

Reputation: 397

postgresql: Splitting multiple columns into rows

Hope you all are well.

Here is screenshot of my dataset![enter image description here]1

Now the real data set currently has almost three thousand rows, and what I'm striving to do is break down the contacts from twelve columns, to three. 

Company name | Contact | Position | Email
FooFoo Inc   |    Me   |   ceo    | stuff@ 
FooFoo Inc   |   You   |   ceo    | stuff@   
FooFoo Inc   |  Friend |   ceo    | stuff@
FooFoo Inc   |  Picard |   cto    | stuff@
Pear Co..... etc.

Any recommendations on how to approach this? Thanks all!

Upvotes: 0

Views: 43

Answers (2)

GMB
GMB

Reputation: 222432

Use UNION ALL:

select company_name, primary_contact contact, contact_position position, email
union all select company_name, contact_2, contact_2_position, contact_2_email
union all select company_name, contact_3, contact_3_position, contact_3_email
union all select company_name, contact_4, contact_4_position, contact_4_email

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Use a lateral join:

select t.company_name, v.*
from t cross join lateral
     (values (t.contact, t.contact_position, t.email),
             (t.contact_2, t.contact_2_position, t.contact_2_email),
             (t.contact_3, t.contact_3_position, t.contact_3_email),
             . . .
     ) v(contact, contact_position, email);

You probably want to add:

where v.contact is not null

but that is not obvious from your sample data.

Upvotes: 1

Related Questions