Solved

# pivot table

Posted on 2009-04-04
Medium Priority
1,036 Views
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
``````
0
Question by:daprjo

LVL 22

Expert Comment

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

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

Author Comment

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

LVL 74

Accepted Solution

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

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

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.