Re: Can I do this with a view?

From: Donna Kray <kray.donna_at_mlink.motors.ge.com>
Date: 1996/07/19
Message-ID: <4so3mi$77k_at_crissy.ge.com>#1/1

Tom Alborough <toma_at_micrologic.com> wrote:
>Hi,
>
>I have Table Unit_Day_Shift:
>
>Unit_ID Day_Date Shift_SN Run_Hours
>=================================================
>123 1-Jan 1 2
>123 1-Jan 2 4
>123 1-jan 3 0
>345 1-jan 1 0
>345 1-jan 2 2
>..
>123 2-jan ...
>
>
>I need Table Unit_Day:
>
>Unit_ID Day_Date Shift_1_Hours Shift_2_Hours Shift_3_Hours
>=====================================================================
>123 1-Jan 2 4 0
>345 1-Jan ...
>
>
 [...]
>
>Is this doable with a view?
>
>Thanks,
>Tom

---

Assuming Unit_ID, Day_Date, SHift_SN is the key :

select Unit_ID, Day_Date, 
       decode(shift_sn, 1, run_hours, 0) "Shift_1_Hours",
       decode(shift_sn, 2, run_hours, 0) "Shift_2_Hours",
       decode(shift_sn, 3, run_hours, 0) "Shift_3_Hours"

from Unit_Day_Shift;

Since you seeem to have accounted for all 3 shifts, you don't need the ",0" argument in the decode function.

I wasn't sure what you need for your other example, but maybe this will get the gears moving.

Hope it helps,

DL Kray Received on Fri Jul 19 1996 - 00:00:00 CEST