Solved

Oracle Sql Pivot Table?

Posted on 2001-08-30
14
3,932 Views
Last Modified: 2008-03-03
This must be a common question:
can Oracle Pivot?

I have data such as
Name  HoursWorked WorkWeek
Me     100        1/01/2001
You    200        1/01/2001
Me     100        1/08/2001
You    200        1/08/2001

And I want the result of a query to be
Name   1/01/2001  1/08/2001
Me       100        100
You      200        200

I am using Oracle Sql Plus 3.3.4.0.0
I have just started to use Oracle so Be Gentle
I've been using access and have used SQL 7.0.
0
Comment
Question by:KanCas
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 5

Expert Comment

by:ser6398
ID: 6442797
I don't know how to get that kind of result in SQL Plus, but I can tell you how to get it in Oracle Reports.

In Oracle Reports, start a new report and create a Matrix Report.  Enter in the basic select statement that returns all the data from the table.  Then it asks you for the columns and grouping.  You can make WorkWeek values be column headers, showing the results you listed.  Just follow the Matrix Report Wizard and you should get those results.
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6443305

Hi,

I have created a dummy table to show you the result as
SQL> desc testsam
 Name                            Null?    Type
 ------------------------------- -------- --------------
 NAME                                     VARCHAR2(10)
 HOURSWORKED                              NUMBER(5)
 WORKWEEK                                 VARCHAR2(10)


let us create a  x.sql as

x.sql
------
select name,decode(workweek,'1/01/2001',hoursworked,0) "01/01/2001",decode(workweek,'1/08/2001',hoursworked,0) "01/08/2001" from testsam;

SQL> @x.sql

NAME       01/01/2001 01/08/2001
---------- ---------- ----------
Me                100          0
Me                  0        100
You                 0        200
You               200          0


if the table structure is like this, where workweek is a date type as follows
SQL> desc testsam
 Name                            Null?    Type
 ------------------------------- -------- --------------
 NAME                                     VARCHAR2(10)
 HOURSWORKED                              NUMBER(5)
 WORKWEEK                                 DATE

Then you have to write a sql statement as

select name,decode(workweek,'01/01/2001',hoursworked,0) "01/01/2001",decode(workweek,'01/08/2001',hoursworked,0) "01/08/2001" from testsam2;

NAME       01/01/2001 01/08/2001
---------- ---------- ----------
Me                100          0
Me                  0        100
You                 0        200
You               200          0

Is that what you want ?

Goodluck
Sameer
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6443336
If you have oracle version 8.x then you can use oracle new feature named as CASE

Recall the above testsam table and let us again create a x.sql as

x.sql
------
  select name,
sum(case when workweek='1/01/2001' then hoursworked else 0 end) "01/01/2001",
sum(case when workweek='1/08/2001' then hoursworked else 0 end) "01/08/2001"
        from testsam group by name
/


Here is the output
SQL> @x.sql (USING CASE)

NAME       01/01/2001 01/08/2001
---------- ---------- ----------
Me                100        100
You               200        200


Sample data in the table was

SQL> select * from testsam;

NAME       HOURSWORKED WORKWEEK
---------- ----------- ----------
Me                 100 1/01/2001
Me                 100 1/08/2001
You                200 1/08/2001
You                200 1/01/2001

if the table structure is like this, where workweek is a date type as follows
SQL> desc testsam
Name                            Null?    Type
------------------------------- -------- --------------
NAME                                     VARCHAR2(10)
HOURSWORKED                              NUMBER(5)
WORKWEEK                                 DATE

Then you have to write a sql statement USING CASE AS

X2.sql (USING CASE)
--------
 select name,
sum(case when to_char(workweek,'DD/MM/YYYY')='01/01/2001' then hoursworked else 0 end) "01/01/2001",
sum(case when to_char(workweek,'DD/MM/YYYY')='01/08/2001' then hoursworked else 0 end) "01/08/2001"
        from testsam2 group by name
/


SQL> @x2.sql

NAME       01/01/2001 01/08/2001
---------- ---------- ----------
Me                100        100
You               200        200


I think output is waht you expected.

Cheers
Sameer
0
 

Expert Comment

by:oswaldocastro
ID: 6444135
Hi KanCas

 A little modification to the 'decodes' above will allow you to have any date on your aplication and not necessarily the initial date of week. Ex:

select
 name
,sum(decode(to_char(WORKDAY,'WW'),32,hoursworked,0) week32
,sum(decode(to_char(WORKDAY,'WW'),33,hoursworked,0) week33
...

and so on.

Despite all of this works it is important to ask if this is the better solution, since the very hard-coding inside the SQL. May be other front-end tools like Excel, Oracle Discovery, Cognos Powerplay and the like will give a more powerful solution.

Stay in touch
Oswaldo
0
 

Author Comment

by:KanCas
ID: 6445016
To All, Great answers But I guess my question would be
Is there sql as powerfull as: (ACCESS CREATES THIS STATEMENT WITH THE CROSSTAB

SELECT Name,HrsWorked,Sum(hrs) as Hrs
FROM TableName
PIVOT Format([Workweek],"yyyymmdd");

The fact is I'm Charting a whole year and do not want to have to create 52 lines of decodes...


0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6445065
Hi KanCas,

The thing which you want is not possible in SQL.  Although you can write a PL/SQL procedure and call it though application in this way you do not have to write a 52 lines if code.

Goodluck
Sam...
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6445110
You can do with it a Matrix report in Oracle reports.  SQL is not meant to be a fully functional reporting application.  Oracle reports is meant to be a fully functional report application (similar to Crystal Reports or Access Reports).  See my description above to do it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:oswaldocastro
ID: 6446912
Ok KanCas
Maybe all you need is just ms access.

Oswaldo
0
 

Author Comment

by:KanCas
ID: 6453345
Increased points to 500 to anyone that can tell me how to do a query like


SELECT Name,HrsWorked,Sum(hrs) as Hrs
FROM TableName
PIVOT Format([Workweek],"yyyymmdd");

you can do in ms access , I do not want to use access the user will have to issue a password every time.... If i have to i will , but I figured i'd give it another shot if there was a solution.

KC
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6453388
Is there some reason that you seem to be completely ignoring my suggestion to use Oracle Developer Report Builder?  As I said in a previous post, SQL*Plus is not the best tool to produce complex reports from an Oracle database.  Oracle Developer Report Builder is.
0
 

Author Comment

by:KanCas
ID: 6453432
I am using MS VISUAL BASIC AND ADO and it seems like the queries designed in SQL PLUS work with ADO. So given that, Oracle Developer Report is kinda outta the question isn't it?
0
 
LVL 5

Accepted Solution

by:
ser6398 earned 200 total points
ID: 6453480
You could still build an Oracle Report as a stand-alone solution that is called from VB.

But if you are using VB you could also look into Crystal Reports.  One other possibility is to return the data from the normal query, store it in a VB array, and then do some programming in VB to order it and display it in the fashion you want.

I don't think you will be able to get the results you want from Oracle SQL alone.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7034746
Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Please click your Member Profile, expand View Question History to navigate and update all your open and locked questions.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7057595
Zero response from Asker or Experts in terms of closing recommendations, sorry to say.
Finalized today by Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now