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 :
Branch        Jan       Feb     Mar   Apr
01                1500   1300   2000 3000
02                2000   1000   2000 4000
03                3000    1500  3000  4500 

Open in new window


same requirement for week number of the year
note : database i'm connection to is 9i
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
select * from (
 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
;
0
NiceMan331Author Commented:
what about week number of the year ?
0
awking00Commented:
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','06','07','08','09','10','11','12','13','14','15','16','17','18')
)
order by branch_no
;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NiceMan331Author Commented:
sorry , the pivot not working with me , i'm using oracle 9i
0
slightwv (䄆 Netminder) Commented:
Try the code below.  You'll just need to add the rest of the months and weeks:
select branch_no,
	sum(case when to_char(trans_date,'Mon') = 'Jan' then trans_amount else 0 end) Jan,
	sum(case when to_char(trans_date,'Mon') = 'Feb' then trans_amount else 0 end) Feb,
	sum(case when to_char(trans_date,'Mon') = 'Mar' then trans_amount else 0 end) Mar,
	sum(case when to_char(trans_date,'Mon') = 'Apr' then trans_amount else 0 end) Apr
from my_trans
group by branch_no
/

select branch_no,
	sum(case when to_number(to_char(trans_date,'IW')) = 1 then trans_amount else 0 end) week_1,
	sum(case when to_number(to_char(trans_date,'IW')) = 2 then trans_amount else 0 end) week_2,
	sum(case when to_number(to_char(trans_date,'IW')) = 3 then trans_amount else 0 end) week_3,
	sum(case when to_number(to_char(trans_date,'IW')) = 4 then trans_amount else 0 end) week_4
from my_trans
group by branch_no
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
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
0
slightwv (䄆 Netminder) Commented:
I do not understand what you mean by start week and end week.

Please post sample results.
0
NiceMan331Author Commented:
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
0
slightwv (䄆 Netminder) Commented:
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:
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
/

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.