Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

pivot table

Posted on 2009-04-04
5
Medium Priority
?
1,024 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

971 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