|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/12/2009 10:43:58 AM
Posts: 1,
Visits: 3
|
|
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,
NKrish
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/19/2009 6:44:26 PM
Posts: 38,
Visits: 409
|
|
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...
|
|
|
|