Link to home
Start Free TrialLog in
Avatar of pm-archtect
pm-archtectFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Oracle 11g Pivot variable set of values

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).
Avatar of Sean Stuber
Sean Stuber

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
Avatar of pm-archtect


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?
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
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,
                 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
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
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.
Thank you - I will experiment with the options above and post back.

Much obliged :-)

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?
Avatar of Sean Stuber
Sean Stuber

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Worked a treat - thank you for all your guidance.