Solved

pivot table

Posted on 2009-04-04
5
1,014 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 73

Accepted Solution

by:
sdstuber earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

808 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