Hi,

I am trying to migrate the data from 1 table to another another.

My current table like below:
create table Employee(
emp_id NUMBER NOT NULL,
emp_setup_type VARCHAR2(10) NOT NULL,
emp_col_1 NUMBER DEFAULT 0,
emp_col_2 NUMBER DEFAULT 0,
emp_col_3 NUMBER DEFAULT 0,
emp_col_4 NUMBER DEFAULT 0,
emp_col_5 NUMBER DEFAULT 0,
primary key (emp_id,emp_setup_type)
)

which has values
insert into Employee values (100,'type1',0,0,2,0,0);
insert into Employee values (100,'type2',0,5,4,0,0);
insert into Employee values (100,'type3',0,0,0,0,5);

insert into Employee values (200,'type1',3,0,2,0,0);
insert into Employee values (200,'type2',0,0,0,8,0);
insert into Employee values (200,'type3',0,4,0,7,5);


Every employee has 3 types,and several emp_col_x columns.I am eliminating all the 0's which is default and will be migrating only those columns has values > 0 for types.


My NEW TABLE structure:
create table Employee_New(
emp_id NUMBER NOT NULL,
setup_id NUMBER NOT NULL,
attribute_col VARCHAR2(10),
emp_type_1 NUMBER,
emp_type_2 NUMBER,
emp_type_3 NUMBER,
primary key (emp_id,setup_id)
)

out put of the new table will look like
emp_id setup_id attribute_col emp_type_1 emp_type_2 emp_type_3
100 1 emp_col_3 2 4 0
100 2 emp_col_2 0 5 0
100 3 emp_col_5 0 0 5


brief explanation :
'Type' was in row format in old table,and converted to column in new table.For same employee i have 3 rows in old table whereas in new table it will be only one row.
For emp_id 100 and emp_col_1 is 0 for all types in old table,it is not migrated.But for emp_id 100 and emp_col_2 has value 5 for type 2.This will be migrated as 0,5,0 since atleast one type value is greater than 0.

I will be writing pl/sql since my old table has around 5000 rows.

I have tried using pivot to convert row to columns,but i am not able to exclude the condition where if all types are '0' for employeeid then exclude that emp_col_x. if any type has value then do not exclude.
I have tried various ways to achieve above result but not successful yet.Can you help me how to achieve this.

create table Employee(
emp_id NUMBER NOT NULL,
emp_setup_type VARCHAR2(10) NOT NULL,
emp_col_1 NUMBER DEFAULT 0,
emp_col_2 NUMBER DEFAULT 0,
emp_col_3 NUMBER DEFAULT 0,
emp_col_4 NUMBER DEFAULT 0,
emp_col_5 NUMBER DEFAULT 0,
primary key (emp_id,emp_setup_type)
);

insert into Employee values (100,'type1',0,0,2,0,0);
insert into Employee values (100,'type2',0,5,4,0,0);
insert into Employee values (100,'type3',0,0,0,0,5);
insert into Employee values (200,'type1',3,0,2,0,0);
insert into Employee values (200,'type2',0,0,0,8,0);
insert into Employee values (200,'type3',0,4,0,7,5);

select * from (
  select * from (
    select * from employee
    unpivot (val for col in (
      emp_col_1 as 'EMP_COL1', emp_col_2 as 'EMP_COL2',emp_col_3 as 'EMP_COL3',
      emp_col_4 as 'EMP_COL4', emp_col_5 as 'EMP_COL5'
      )
    )
  )
  pivot (min(val) for (emp_setup_type) in ('type1' TYPE1, 'type2' TYPE2, 'type3' TYPE3))
)
where  not (
  type1 = 0 and type2 = 0 and type3 = 0
);

EMP_ID  COL       TYPE1  TYPE2  TYPE3  
100     EMP_COL2  0      5      0      
200     EMP_COL1  3      0      0      
200     EMP_COL2  0      0      4      
200     EMP_COL4  0      8      7      
200     EMP_COL5  0      0      5      
100     EMP_COL5  0      0      5      
100     EMP_COL3  2      4      0      
200     EMP_COL3  2      0      0