Oracle 11g Pivot variable set of values

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?

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,
                 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

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

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 :-)

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?

Worked a treat - thank you for all your guidance.