pivot sql

[JeanneZ]

Hi, SQL experts,

I need help. I have a table like:

SERV_MONTH R NUM
JANUARY R01 19
JANUARY R02 2
JANUARY R03 2
JANUARY R04 3
FEBRUARY R01 1
FEBRUARY R02 1
MARCH R03 1
APRIL R02 1
MAY R02 1
MAY R03 1
JUNE R01 1
JUNE R03 1
JULY R01 2
JULY R02 1
JULY R03 2
JULY R04 1
AUGUST R01 1
AUGUST R02 1
AUGUST R04 1
SEPTEMBER R01 7
SEPTEMBER R02 4
SEPTEMBER R03 4
SEPTEMBER R04 2
OCTOBER R01 13
OCTOBER R02 8
OCTOBER R03 6
OCTOBER R04 4
NOVEMBER R01 1
NOVEMBER R03 1

Based on the above table, I will create a report with "start_month" and "end_month" parameters. Thus the report will display rows in the above table as ccolumns, and columns in the above table as rows. The SERV_MONTH is based on the parameters in the rows.
For example, if user selects start_month = ‘MAY’ and end_month=’OCTOBER’, the report should be:
MAY JUNE JULY AUGUST SEPTEMBER OCTOBER
R01 1 1 2 1 7 13
R02 1 0 1 1 4 8
R03 1 1 2 0 4 6
R04 0 0 1 1 2 4

Any help would be greatly appreciated.

 

[SantaMufasa]

Jeanne,

What code have you tried already? If you haven't tried specific code yet, can you assert a non-syntactical "functional narrative" of what are ways to solve your need? What is your skill level with SQL and PL/SQL?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[JeanneZ]

Mufasa,

I have tried to use dynamic sql inside pl/sql. But I need a sql to get the result. It will embed in Coldfusion.

Thanks.

 

[Thargy]

Jeanne,

I've just asked Tom, and he has an excellent solution.

try

http://asktom.oracle.com/pls/ask/f?...0_P8_DISPLAYID,F4950_P8_CRITERIA:766825833740

for size.


Regards

Tharg

Grinding away at things Oracular

 

[JeanneZ]

I can not hard code serv_month. Please give me a sample SQL. Thanks.