CODEWITHSUNDEEP
sqlpostgresql
user2926497
user2926497

Reputation: 515

Create New Records based on the old one

I have an old table as below. We are modifying the data structure.

Employee:

Emp_ID  Dep   profile
345     808   16
345     813   14
345     809

I will have to populate records in a new table as below:

Employee:

Emp_ID   Dep    Profile
345       808    16
345       808    14
345       813    16
345       813    14
345       809    16
345       809    14

How do I do this in Postgres?

Upvotes: 0

Views: 42

Answers (1)

klin
klin

Reputation: 121504

You need some kind of cross join in groups by emp_id:

select *
from (
    select emp_id, dep
    from employee
    where dep is not null
    ) t1
join (
    select emp_id, profile
    from employee
    where profile is not null
    ) t2 using(emp_id)

 emp_id | dep | profile 
--------+-----+---------
    345 | 808 |      16
    345 | 808 |      14
    345 | 813 |      16
    345 | 813 |      14
    345 | 809 |      16
    345 | 809 |      14
(6 rows)

I have to add I do not like this new model. A normalized solution might look like this:

create table employees(
    emp_id int primary key);

create table departments(
    dep_id int, 
    emp_id int references employee,
    primary key (dep_id, emp_id));

create table profiles(
    profile_id int, 
    emp_id int references employee,
    primary key (profile_id, emp_id));

Upvotes: 1

Related Questions