NiceMan331
asked on
Pivot Data Of Table By Month/Or Week
hi
i simply have table My_Trans
Trans_ID number
Branch_No Number
Trans_Amount Number
Trans_Date Date
how i can generate pivot query from the above table like this :
same requirement for week number of the year
note : database i'm connection to is 9i
i simply have table My_Trans
Trans_ID number
Branch_No Number
Trans_Amount Number
Trans_Date Date
how i can generate pivot query from the above table like this :
Branch Jan Feb Mar Apr
01 1500 1300 2000 3000
02 2000 1000 2000 4000
03 3000 1500 3000 4500 same requirement for week number of the year
note : database i'm connection to is 9i
ASKER
what about week number of the year ?
Same way -
select * from (
select branch_no, trans_amount, to_char(trans_date,'WW') week
from my_trans)
pivot
(
sum(trans_amount)
for week in ('01','02','03','04','05',
)
order by branch_no
;
select * from (
select branch_no, trans_amount, to_char(trans_date,'WW') week
from my_trans)
pivot
(
sum(trans_amount)
for week in ('01','02','03','04','05',
)
order by branch_no
;
ASKER
sorry , the pivot not working with me , i'm using oracle 9i
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes slightw it is ok for months
but regarding weeks , it will be very smart if you adjust the code to add start week and end week
to the beginning of the sql , then to add the weeks by for loop if you could do it
thanx
but regarding weeks , it will be very smart if you adjust the code to add start week and end week
to the beginning of the sql , then to add the weeks by for loop if you could do it
thanx
I do not understand what you mean by start week and end week.
Please post sample results.
Please post sample results.
ASKER
i mean the year has 52 weeks
instead of repeating the code 52 times
if i need only weeks between 40 and 48 for example
let the code begin loop between 40 and 48
then to add the code 8 times with increment of week number
instead of repeating the code 52 times
if i need only weeks between 40 and 48 for example
let the code begin loop between 40 and 48
then to add the code 8 times with increment of week number
If you want 8 weeks from the current date's week number try this (just keep adding weeks).
Just grab the week number from sysdate:
Just grab the week number from sysdate:
select branch_no,
sum(case when to_number(to_char(trans_date,'IW')) = to_number(to_char(sysdate,'IW')) then trans_amount else 0 end) week_1,
sum(case when to_number(to_char(trans_date,'IW')) = to_number(to_char(sysdate+7,'IW')) then trans_amount else 0 end) week_2,
sum(case when to_number(to_char(trans_date,'IW')) = to_number(to_char(sysdate+14,'IW')) then trans_amount else 0 end) week_3,
sum(case when to_number(to_char(trans_date,'IW')) = to_number(to_char(sysdate+21,'IW')) then trans_amount else 0 end) week_4
from my_trans
group by branch_no
/
select branch_no, trans_amount, to_char(trans_date,'MON') month
from my_trans)
pivot
(
sum(trans_amount)
for month in ('JAN','FEB','MAR','APR')
)
order by branch_no
;