RE: tricky pivot query
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <ricks12345@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Jun 2008 00:39:53 -0400
You can use case statements to get the data you want. I'm not sure I understand your structure at this hour, but I'll give you an idea and maybe you can fill in the rest. Select day_id Sum(Case when to_date(day_id,' YYYYMMDD') > trunc(sysdate,'MM') then sales_rollup else 0 end) sales_1, Sum(Case when to_date(day_id,' YYYYMMDD') between add_month(trunc(sysdate,'MM'),-1) and trunc(sysdate,'MM')-1 then sales_rollup else 0 end) sales_2, Sum(Case when to_date(day_id,' YYYYMMDD') between add_month(trunc(sysdate,'MM'),-2) and trunc(sysdate,'MM')-1 then sales_rollup else 0 end) sales_3, From . Where .. Group by day_id; _____ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rick Ricky Sent: Tuesday, June 10, 2008 11:29 PM To: oracle-l@xxxxxxxxxxxxx Subject: tricky pivot query I am pretty sure I need to use the model clause to do this efficient. I am reading the docs and examples, but I am a bit stumped. I have two tables daily_summary ( day_id number, empno number, sales_rollup number); monthly_summary ( month_id number, empno number, sales_rollup number); daily_summary table day_id is a number representing a date of the form YYYYMMDD. This is a group by of the sales for a given day monthly_summary month_id is a number representing a date of the form YYYYMM this is a rollup of daily_summary by month. I am given an empno. I need to return empno, sales_0,sales_1,sales_2,sales_3 sales_0 current months sales. So if this is june 10th, I need to rollup the first 10 days of the month from the daily_summary sales_1 is last months rollup of sales (I have this in monthly_summary) sales_2 is 2 months ago rollup of sales sales_3 is 3 months ago rollup of sales I am doing this in the database and passing back a ref cursor. So i need a pivot query, I need a group by (I think) and I need it to be dynamic. any suggestions? The examples are all more basic than this. am I going in the correct direction with the model clause? I really don't want to use alot of pl/sql to build this...
- tricky pivot query
- From: Rick Ricky
- tricky pivot query