Oracle 11g Pivot variable set of values

pm-archtect
pm-archtect used Ask the Experts™
on
Hi (this is a revised version of a question I asked previously - hopefully made clearer).

I need to pivot a result set based on a range of dates (wherby the dates become the column headers).  Prior to retrieving the data, the user would choose a range of dates working backwards e.g. 1st June 2012 to 15th May 2012.

I appreciate that varying the number dates in the result set will affect the number of columns when you pivot the data (which in itself might prevent the ability to pivot (but I don't know)).

If it's not possible to vary the number of columns, is it possible to always have a sliding window of 31 days i.e.  

<user selected date> to <user selected date minus 31 days>

so that the number of columns remains the same.

I attach a small sample of data show how I need the pivot to occur.  It only contains 2 days worth of data, but I hoping that whether its 2 days, 5 days or 30 days, the logic should be the same (the loop count would just grow).
Sample-Data.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can't have dynamic columns in sql,  the columns to be returned (both the number and name of them) must be known at the time the sql is parsed.

you can have a sliding window though

Author

Commented:
Thank you for responding

When you say it is possible to have a sliding window, can you explain what you mean please as I guess it doesn't mean what I am thinking.  As you say, the column names must be known and because the dates will be different each time, then the column names will be forever changing.

Is there an alternative approach to achieve something similar please?
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, you can't have column names that are always changing, but if you what you are looking for is a 31 day window then I'd just name them 1-31

something like this

select * from
(select unitid,kilometrage,row_number() over(partition by unitid order by datereceived desc) rn from yourtable
where datereceived < to_date('2012/05/25','yyyy/mm/dd')
and datereceived > to_date('2012/05/25','yyyy/mm/dd')- 31
) pivot (max(kilometrage) for rn in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31))
order by unitid
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
and here's an alternate version SELECT   unitid,
         MAX(DECODE(rn, 1, kilometrage)) "1",
         MAX(DECODE(rn, 2, kilometrage)) "2",
         MAX(DECODE(rn, 3, kilometrage)) "3",
         MAX(DECODE(rn, 4, kilometrage)) "4",
         MAX(DECODE(rn, 5, kilometrage)) "5",
         MAX(DECODE(rn, 6, kilometrage)) "6",
         MAX(DECODE(rn, 7, kilometrage)) "7",
         MAX(DECODE(rn, 8, kilometrage)) "8",
         MAX(DECODE(rn, 9, kilometrage)) "9",
         MAX(DECODE(rn, 10, kilometrage)) "10",
         MAX(DECODE(rn, 11, kilometrage)) "11",
         MAX(DECODE(rn, 12, kilometrage)) "12",
         MAX(DECODE(rn, 13, kilometrage)) "13",
         MAX(DECODE(rn, 14, kilometrage)) "14",
         MAX(DECODE(rn, 15, kilometrage)) "15",
         MAX(DECODE(rn, 16, kilometrage)) "16",
         MAX(DECODE(rn, 17, kilometrage)) "17",
         MAX(DECODE(rn, 18, kilometrage)) "18",
         MAX(DECODE(rn, 19, kilometrage)) "19",
         MAX(DECODE(rn, 20, kilometrage)) "20",
         MAX(DECODE(rn, 21, kilometrage)) "21",
         MAX(DECODE(rn, 22, kilometrage)) "22",
         MAX(DECODE(rn, 23, kilometrage)) "23",
         MAX(DECODE(rn, 24, kilometrage)) "24",
         MAX(DECODE(rn, 25, kilometrage)) "25",
         MAX(DECODE(rn, 26, kilometrage)) "26",
         MAX(DECODE(rn, 27, kilometrage)) "27",
         MAX(DECODE(rn, 28, kilometrage)) "28",
         MAX(DECODE(rn, 29, kilometrage)) "29",
         MAX(DECODE(rn, 30, kilometrage)) "10",
         MAX(DECODE(rn, 31, kilometrage)) "31"
    FROM (SELECT unitid,
                 kilometrage,
                 ROW_NUMBER() OVER (PARTITION BY unitid ORDER BY datereceived DESC) rn
            FROM yourtable
           WHERE     datereceived < TO_DATE('2012/05/25', 'yyyy/mm/dd')
                 AND datereceived > TO_DATE('2012/05/25', 'yyyy/mm/dd') - 31)
GROUP BY unitid
ORDER BY unitid

Author

Commented:
Just out of interest (and perhaps this isn't the forum for this question):

I would imagine that this is quite a common issue i.e. pivoting dates (or a desire to do so).  If I wanted to get the dates as column headers - are there other ways that you know of.  

I attach an example of the output (this is generated from a legacy system that I'm pretty sure acheives it's results via coding).

One thing I keep thinking - could Crystal cross-tabulate this data?  Again, I appreciate this is a Crystal question and I'm in the Oracle forum (I'm just hoping to draw from your experience as perhaps that is the solution :-)

Kind regards
Sample-Pivot.xlsx
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can't do it in sql, at all - in any database.  It's not an oracle thing, it's part of the sql language.

You could use dynamic sql.  That is,  procedural logic to construct a query on the fly that pulls what you need and names the columns as appropriate.

Similarly you could use various reporting tools to read the fixed names and apply new names as part of showing the results but again, that's not really sql anymore.

 If you want to know how to do that, I suggesting posting a new question there.  I'm not really a crystal user, but I'm pretty sure it should be possible, I just don't know how.

Author

Commented:
Thank you - I will experiment with the options above and post back.

Much obliged :-)

Author

Commented:
Hi

I had a thought this morning; it might not be the most elegant solution but it would provide a view of the data that users could work with.

If the column headers need to remain static and the number of columns must remain the same, would it be possible to output 31 columns  named "31" to "1" and then place the data associated with each month day in the correct column.

If this is possible, the icing on the cake would then be to order the columns based on the starting point i.e. if the  user wants to report from the 15th of the month, then the columns would be ordered:

15, 14, 13, 12,11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16

This would enable the user to visulalize where the the month boundary is.  The report header can show the actual start and end dates in full.

For those columns that contain no data (e.g. for months with only 30 days where the data would be missing), we could perhaps display a 0 (zero).

Is this too difficult please?
Most Valuable Expert 2011
Top Expert 2012
Commented:
it's not possible to change the ordering on the fly.

It definitely IS possible to have 31 columns number 1-31 (that's already done above)

in the queries above 1 is the last (most recent) day and 31 is the oldest day in the range

if you wanted the numbers to map to the days of the month  then take out the row_number call  and replace it with  to_number(to_char(datereceived,'dd'))

Author

Commented:
Worked a treat - thank you for all your guidance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial