Avatar of pm-archtect
Flag 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).
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon
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

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

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Sean Stuber

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
Sean Stuber

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Worked a treat - thank you for all your guidance.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.