I would appreciate any assistance you could provide on this query, I'm assuming the easiest way to do this would be using the PIVOT function. I've been reading through some of the documentation and books, and done some searching here in the forums, but can't seem to find a way to make this work.

We get this data each month - Month_A is always the most recent month, and it goes back, so for this set, Month_A is November 09, Month_B is October 09, Month_C is Sept 09, etc. I am OK with hard-coding this value each month, so for the purposes of this exercise, just assume that Month_A is November 09, Month_B is Oct 09, Month_C is Sept 09, and Month_D is Aug 09.

And I basically need to "Pivot" this table, so the end result looks like this:

Below is the SQL to create the base table with test data. Now that I've typed up this explanation, it seems even simpler than I had made it out to be... but I'm still stumped, so any help is greatly appreciated, thanks!!

tags around your code to preserve the formatting (makes it easier to read).
And, from what you've posted, i couldn't discern where the MONTH information is being derived, can you explain that in better detail?

tags, so it lines up better now. I also added a better explanation of how to derive the months from the columns - basically, Month_A is Nov 09, Month_B is Oct 09, Month_C is Sept 09 and Month_D is Aug 09.
I'll look at the link that was posted, see if that helps.

I don't have an 11 install sitting around, so i can't test the PIVOT option, but you should be able to work that in to this (replacing the MAX(DECODE( code ) if you have an unknown number of Products....

THANK YOU!! EXCELLENT, this is precisely the kind of thing I was looking for!! To further challenge you, I actually have it setup where I have 2 different totals/amounts for each month that I need to track separately. So in reality, my table looks like this:

I tried taking your code and tweaking it to do this, but having issues. Does it have to have a whole separate PIVOT and UNPIVOT clause for the amt_2 values?? That is what I tried below, but getting errors about "column ambiguously defined". Again, help is GREATLY appreciated here!!

You don't want to do multiple UNPIVOT and PIVOT clauses; you want to do multiple sets of columns in the one UNPIVOT clause and the one PIVOT cluase.

In the UNPIVOT clause, it's jsut a matter of replacing the single column "amt" before the FOR keyword with a parenthesized list "(amt_1, amt_2)", and replacing each column in the IN list (e.g. "month_a_amt") with a list of the same length ("(month_a_amt_1, month_a_amt_2)").
In the PIVOT clause, it's just a matter of replacing the single aggregate function "SUM (amt)" with an unparenthesized list of functions, each with an alias ("SUM (amt_1) AS amt_1, SUM (amt_2) AS amt_2". The aliases will automaticallly be added to the end of the output column names givien in the IN clause.

It looks like all the new amt2 values are 9. Do you think that's the best test? I think different numbers, like you used for the earlier sample data, would reduce the chances of getting the right results purely by coincidence.

If you want to experiment with queries like this, I suggest you use "SELECT *" (nothing else added) as the SELECT clause. Start with just an UNPIVOT operation. Some examples in the documentation do a CREATE TABLE AS ... to save the results of an UNPIVOT, and use that table as the base table for a PIVOT. I think that's a neat idea for reducing confusion.

Frank, you are THE MAN, thank you!!! I really appreciate you taking the time to look into this, and provide me the example. Not only the code, but the nice explanation, so I can figure out how it is all working!! I was new to these PIVOT and UNPIVOT functions, I think I had a solid understanding of using them each separately, but it gets confusing when you slap the 2 together!!