?
Solved

pivot table

Posted on 2009-04-04
5
Medium Priority
?
1,036 Views
Last Modified: 2013-12-19
Dear  Experts,
                What is pivot table. Why we have to use pivot table  to generate required number of rows.
Please explain me what is the use of pivot table.


1  select  sum( case when  to_char(josh_date + t500.id -1 ,'Dy') in ('SAT','SUN')
  2  then  0  else  1 end) as days  from (
  3  select max(case when first_name = 'priya' then hire_date end ) as priya_date,
  4         max( case when first_name = 'joshua' then hire_date end) as josh_date
  5  from employee
  6  where  first_name  in ('priya','joshua') ) x,t500
  7* where   t500.id <=  priya_date - josh_date  + 1
SQL> /
where  first_name  in ('priya','joshua') ) x,t500
 
Error comes :
Table or view not exist

Open in new window

0
Comment
Question by:daprjo
5 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 24066832
A pivot table (AKA cross tab) is a type of report or tool that summarises data in rows and columns. It has nothing particular to do with SQL tables or queries. You can use a SQL query to simulate the same kind of result but SQL isn't a report development language so pivot functionality is usually better done using presentation and reporting tools instead of SQL.
0
 

Author Comment

by:daprjo
ID: 24067231
Dear Expert,
  Then how i can use it oracle for someother purpose.
0
 

Author Comment

by:daprjo
ID: 24067232
Dear Expert,
  Then how i can use pivot table in oracle .
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24067521
your inner query is the pivot portion

select max(case when first_name = 'priya' then hire_date end ) as priya_date,
         max( case when first_name = 'joshua' then hire_date end) as josh_date
 from employee
where first_name  in ('priya','joshua')

you use it exactly as you've shown,  you have different names in multiple rows but your goal is to have them in a single row.

Another common use would be monthly data that you would want to be reported on a single row with all 12 months for each year.




0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24069300
As simple an explanation and picture of a pivot table as I know is here on Wikipedia:

http://en.wikipedia.org/wiki/Cross_tab

As how to implement one, Sean has given an example above that is pretty simple.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question