Reporting Data

Analytics

Reporting Data

I have a table like this.

Emp| Loc| Work_OT| Rate_OT| Amt_OT| Work_NT| Rate_NT| Amt_NT| Work_NO| Rate_NO| Amt_NO

1 10 10 8 80 10 10 100 10 12 120

I want the result like this.

Emp Loc Work Rate Amount Type

1 10 8 10 80 OT

1 10 10 10 100 NT

1 10 10 12 120 NO

Kindly suggest an SQL. I tried using Union and CASE, But dint work..

Thanks and Regards,
1 REPLY
Enthusiast

Re: Reporting Data

Hi,
I tried the below query and it worked

create volatile table abc1(
emp integer,
loc integer,
work_ot integer,
rate_ot integer,
amt_ot integer,
work_nt integer,
rate_nt integer,
amt_nt integer,
work_no integer,
rate_no integer,
amt_no integer
) primary index ( emp,loc) on commit preserve rows;

insert into abc1(1,10,10,8,80,10,10,100,10,12,120);

sel
emp,
loc,
work_ot as col1,
rate_ot as col2 ,
amt_ot as col3 ,
'OT' as amnt_type
from abc1
union all

sel
emp,
loc,
work_nt as col1 ,
rate_nt as col2,
amt_nt as col3,
'NT' as amnt_type
from abc1
union all

sel
emp,
loc,
work_no as col1,
rate_no as col2,
amt_no as col3,
'NO' as amnt_type
from abc1

I hope you don't have many columns and just 3 sets of work/rate/amt...