Home » SQL & PL/SQL » SQL & PL/SQL » Variable Pivot, Possible?
Variable Pivot, Possible? [message #412480] Thu, 09 July 2009 12:01 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I'm tasked with creating a summary report (days that a well has spend drilling) and one particular part of it is proving troublesome to me, I believe I've distilled out the basics of the part that is troubling me.

What I have is:

create table temp_pivot
(
    id varchar2(8),
    min   number,
    max     number
);

insert into temp_pivot values ('A',1,5);
insert into temp_pivot values ('B',3,10);
insert into temp_pivot values ('C',9,14);


What I want is a query that will yield the following

A   1
A   2
A   3
A   4
A   5
B   3
B   4
B   5
B   6
B   7
B   8
B   9
B   10
C   9
C   10
C   11
C   12
C   13
C   14


Bonus points if you can do it with dates as opposed to numbers, but I'm certain I can figure that part out on my own, assuming this is possible ...

[Updated on: Thu, 09 July 2009 12:14]

Report message to a moderator

Re: Variable Pivot, Possible? [message #412484 is a reply to message #412480] Thu, 09 July 2009 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just use a standard row generator.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Variable Pivot, Possible? [message #412488 is a reply to message #412484] Thu, 09 July 2009 12:53 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

Also could you give an example of a row generator? I'm not certain what they do, but I'm thinking they aren't going to do what I want them to in this case.
Re: Variable Pivot, Possible? [message #412490 is a reply to message #412488] Thu, 09 July 2009 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
row generator
And they can do what you want.

Regards
Michel

[Updated on: Thu, 09 July 2009 12:58]

Report message to a moderator

Re: Variable Pivot, Possible? [message #412491 is a reply to message #412480] Thu, 09 July 2009 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Also could you give an example of a row generator?
I am sorry to see both SEARCH & GOOGLE are broken for you.
Please wait while repairs are completed.
Additional details will be posted as they become available.

[Updated on: Thu, 09 July 2009 13:00]

Report message to a moderator

Re: Variable Pivot, Possible? [message #412492 is a reply to message #412480] Thu, 09 July 2009 13:20 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Thanks MC, the following is my result:

SELECT   ID, mynum
    FROM temp_pivot,
         (SELECT     ROWNUM mynum
                FROM DUAL
          CONNECT BY LEVEL <= 20)
   WHERE mynum BETWEEN MIN AND MAX
ORDER BY ID, mynum
Re: Variable Pivot, Possible? [message #412560 is a reply to message #412480] Fri, 10 July 2009 00:25 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Bonus points if you can do it with dates as opposed to numbers, but I'm certain I can figure that part out on my own, assuming this is possible ...

 create table temp_pivot1
 (
     id varchar2(8),
     min date,
     max date
 );
 SQL> insert into temp_pivot1 values ('A','1-jul-2009','20-jul-2009');

1 row created.

SQL> ed
Wrote file afiedt.buf

  1*  insert into temp_pivot1 values ('B','1-aug-2009','20-aug-2009')
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1*  insert into temp_pivot1 values ('C','29-jul-2009','1-aug-2009')
SQL> /

1 row created.

Use this select(a little modofication to your query)
  SELECT   ID, min+mynum-1 mydate
     FROM temp_pivot1,
          (SELECT     ROWNUM mynum
                 FROM DUAL
           CONNECT BY LEVEL <= 20)
    WHERE min+mynum-1 BETWEEN MIN AND MAX
 ORDER BY ID, mydate
Previous Topic: Generate multiple file names with fixed format
Next Topic: Number Format (merged 3)
Goto Forum:
  


Current Time: Sat Dec 10 09:05:09 CST 2016

Total time taken to generate the page: 0.04759 seconds