Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pivot table

Posted on 2009-04-04
5
Medium Priority
?
1,023 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

718 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