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

x
?
Solved

Crosstab Query

Posted on 2003-11-23
2
Medium Priority
?
3,024 Views
Last Modified: 2011-09-20
In MS Access, there is a command TRANSFORM... PIVOT... to produce a crosstab query.

Is there any command or functions that is similar to the TRANSFORM... PIVOT... command of MS Access??

0
Comment
Question by:shlikjohn
2 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 9809523
Reporting tools have such features - e.g. Oracle Reports can create "matrix reports".   SQL Plus and the SQL language do not.  A standard way to create a crosstab query in Oracle SQL is:

select s.region,
         sum( decode( s.year, 2001, s.value, 0 )) sales_2001,
         sum( decode( s.year, 2002, s.value, 0 )) sales_2002,
         sum( decode( s.year, 2003, s.value, 0 )) sales_2003,
         ...
from sales s
where ...
group by s.region;
 
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 400 total points
ID: 9810484
The simple answer is: No.  The comment from andrewst describes the two most common ways to accomplish this in Oracle, but they each have disadvantages.  The first approach requires buying (and learning to use) Oracle Reports, and this is not the most user-friendly reporting tool on the market.  The second approach (with "decode") requires you to know and write code for all of the different possible values in the data.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

578 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