Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3986
  • Last Modified:

Oracle Sql Pivot Table?

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
KanCas
Asked:
KanCas
  • 4
  • 3
  • 3
  • +2
1 Solution
 
ser6398Commented:
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
 
WadhwaCommented:

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
 
WadhwaCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
oswaldocastroCommented:
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
 
KanCasAuthor Commented:
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
 
WadhwaCommented:
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
 
ser6398Commented:
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
 
oswaldocastroCommented:
Ok KanCas
Maybe all you need is just ms access.

Oswaldo
0
 
KanCasAuthor Commented:
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
 
ser6398Commented:
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
 
KanCasAuthor Commented:
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
 
ser6398Commented:
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
 
MoondancerCommented:
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
 
MoondancerCommented:
Zero response from Asker or Experts in terms of closing recommendations, sorry to say.
Finalized today by Moondancer - EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now