postgresql: Splitting multiple columns into rows

Question

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!

SELECT individual data groups and UNION them together.

PM 77-1
– PM 77-1

2019-10-15 19:07:01 +00:00
Commented Oct 15, 2019 at 19:07 — PM 77-1
– PM 77-1, Commented Oct 15, 2019 at 19:07

GMB · Accepted Answer · 2019-10-15 19:09:21Z

2

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

answered Oct 15, 2019 at 19:09

GMB

224k25 gold badges102 silver badges151 bronze badges

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

1 Comment

ThirdGhostHand Over a year ago

You're a saint :D

Gordon Linoff · Accepted Answer · 2019-10-15 19:07:29Z

1

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.

answered Oct 15, 2019 at 19:07

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

postgresql: Splitting multiple columns into rows

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related