• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Oracle Pivot table

 
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a problem with an application that I am attempting to create of a legacy table of data. The table format is

datetime(timestamp) value_id(int) value(float).

I need to transform this into the following resultset filtered by a set of at most 9 value_id.

datetime, value_id_1,value_id_1,...,value_id_9

the row values will be datetime with the corresponding float value for the id which is not the column name. I believe I need to use a pivot table but am unfamiliar with this type of solution. If anyone could provide sample code or point me in the right direction that would be great. I can not modify the data structure but can accomplish this with the use of stored procedures on Oracle 9i Standard edition

Thanks in advance...

Jeremy
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jeremy,

From a quick Google, Oracle 9i pivot table syntax is as follows:

Generates 100 rows numbered 1 to 100.

I'm not sure how this helps you with your problem, however.
I've solved that kind of problem before. If you feel you need more help I'll try and remember how.

Jules
 
Jeremy Wilson
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is pivot_package a part of. Do not believe it is part of Oracle Standard edition
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dunno. Try searching Oracle's web site.

No matter. You can achieve the same effect with the following code:

I'd be interested to know how this helps solve the problem.

Jules
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hmmm, that's what happens when you skim an article. Here is the code for pivot_package and its dependencies from oracle.com:

Enjoy!

Jules
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic