Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

pivot table

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

564 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