Cross Tab

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Cross Tab

Postby ponic » Wed Feb 08, 2012 11:37 am

Tim

I am trying to have a cross tab report with month/year on top for each employee like the attached image
http://postimage.org/image/rvk53ijs5/

I have data like the following,
Code: Select all
WITH tab
       AS (SELECT   'MAT' employee,
                    TO_DATE ('08-JAN-2011', 'dd-MON-yyyy') date_starting,
                    TO_DATE ('08-JAN-2011', 'dd-MON-yyyy') date_ending
             FROM   DUAL
           UNION ALL
           SELECT   'SMITH' employee,
                    TO_DATE ('25-MAR-2011', 'dd-MON-yyyy') date_starting,
                    TO_DATE ('05-APR-2011', 'dd-MON-yyyy') date_ending
             FROM   DUAL
           UNION ALL
           SELECT   'PATRICIA' employee,
                    TO_DATE ('13-JUN-2011', 'dd-MON-yyyy') date_starting,
                    TO_DATE ('12-JUL-2011', 'dd-MON-yyyy') date_ending
             FROM   DUAL
           UNION ALL
           SELECT   'KENNY' employee,
                    TO_DATE ('17-JUL-2011', 'dd-MON-yyyy') date_starting,
                    TO_DATE ('03-AUG-2011', 'dd-MON-yyyy') date_ending
             FROM   DUAL
           UNION ALL
           SELECT   'JACK' employee,
                    TO_DATE ('29-AUG-2011', 'dd-MON-yyyy') date_starting,
                    TO_DATE ('31-AUG-2011', 'dd-MON-yyyy') date_ending
             FROM   DUAL)
SELECT   employee, date_starting, date_ending
  FROM   tab       


How can I have Month_Year along with my other columns, would like to have Month_Year for full year.

Thanks
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Cross Tab

Postby Tim... » Wed Feb 08, 2012 12:01 pm

Hi.

- Post CREATE TABLE and INSERT statements (that work) to build a test case.
- Post your current query working against these table(s).
- Post a mock-up of the output you would like to see from the final query, making it sure it matches the test data you have sent.

Once you post all this I will take a look at it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Cross Tab

Postby ponic » Wed Feb 08, 2012 12:21 pm

Create Script
Code: Select all
create table tab
(employee varchar2(32),
date_starting date,
date_ending date);


Insert Script
Code: Select all
Insert into TAB
   (EMPLOYEE, DATE_STARTING, DATE_ENDING)
 Values
   ('MAT', TO_DATE('01/08/2011', 'MM/DD/YYYY'), TO_DATE('01/08/2011', 'MM/DD/YYYY'));
Insert into TAB
   (EMPLOYEE, DATE_STARTING, DATE_ENDING)
 Values
   ('SMITH', TO_DATE('03/25/2011', 'MM/DD/YYYY'), TO_DATE('04/05/2011', 'MM/DD/YYYY'));
Insert into TAB
   (EMPLOYEE, DATE_STARTING, DATE_ENDING)
 Values
   ('PATRICIA', TO_DATE('06/13/2011', 'MM/DD/YYYY'), TO_DATE('07/12/2011', 'MM/DD/YYYY'));
Insert into TAB
   (EMPLOYEE, DATE_STARTING, DATE_ENDING)
 Values
   ('KENNY', TO_DATE('07/17/2011', 'MM/DD/YYYY'), TO_DATE('08/03/2011', 'MM/DD/YYYY'));
Insert into TAB
   (EMPLOYEE, DATE_STARTING, DATE_ENDING)
 Values
   ('JACK', TO_DATE('08/29/2011', 'MM/DD/YYYY'), TO_DATE('08/31/2011', 'MM/DD/YYYY'));
COMMIT;


SQL
Code: Select all
SELECT   employee, date_starting, date_ending
  FROM   tab


I would like to have a period column in the format Month/YYYY along with the other columns from table tab.
period column should have data starting from Jan/2011 to Dec/2011.

Idea of having period column is in my cross tab report I would like to have period defined on top. This will be one of the dimensions in cross tab report.

Regards
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Cross Tab

Postby Tim... » Wed Feb 08, 2012 1:50 pm

Hi.

Please post a mock-up of the required output. Trying to explain it does not make it very clear. Pretend you have run the finished statement in SQL*Plus using the data you have provided. What will the output look like?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Cross Tab

Postby ponic » Thu Feb 09, 2012 6:52 am

Hi Tim

I have query like
Code: Select all
SELECT   employee, date_starting, date_ending
  FROM   tab



What I would like to see is something like this
Code: Select all
                        JAN-2011                      MAR-2011      APR-2011               
MAT          08/Jan/11      08/Jan/11                               
SMITH                                                25/Mar/11   05/Apr/11


How this is could be accomplished?

Thanks
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Cross Tab

Postby Tim... » Thu Feb 09, 2012 10:25 am

Hi.

Not really sure what you are doing with column here. Doesn't exactly look like SQL*Plus output to me. At least now I can see what you are asking for though...

You can't have a single name spanning two columns. SQL Doesn't work that way. Each column has its own name.

The classic solution to this type of issue is shown below.

Code: Select all
SELECT employee,
       (CASE
         WHEN TO_CHAR(date_starting, 'MON') = 'JAN' THEN date_starting
         ELSE NULL
       END) AS date_starting_jan,
       (CASE
         WHEN TO_CHAR(date_ending, 'MON') = 'JAN' THEN date_ending
         ELSE NULL
       END) AS date_ending_jan,
       (CASE
         WHEN TO_CHAR(date_starting, 'MON') = 'MAR' THEN date_starting
         ELSE NULL
       END) AS date_starting_mar,
       (CASE
         WHEN TO_CHAR(date_ending, 'MON') = 'MAR' THEN date_ending
         ELSE NULL
       END) AS date_ending_mar
FROM   tab;

EMPLOYEE                         DATE_STARTI DATE_ENDING DATE_STARTI DATE_ENDING
-------------------------------- ----------- ----------- ----------- -----------
MAT                              08-JAN-2011 08-JAN-2011
SMITH                                                    25-MAR-2011
PATRICIA
KENNY
JACK

5 rows selected.

test@db11g>


The problem is you need to know in advance what fields you want to display, then create a CASE entry for each field. Most of the time this is not what people want.

In 11g, Oracle introduced the PIVOT and UNPIVOT operators.

http://www.oracle-base.com/articles/11g ... _11gR1.php

These replace the code listed above, but still require you to know the output you desire in advance of writing the query. Trouble is, you are trying to pivot based on two values (start and end dates), rather than one, so I don't see this as a clean solution for that. Especially since the start and end dates may not be in the same month.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Cross Tab

Postby ponic » Thu Feb 09, 2012 10:41 am

Tim

Thanks for the help.
As a matter of fact I am trying to have cross tab with a reporting tool and I am find ways to have dimensions for date range and hence all the confusions. Once I am clear on how to create dimensions I will come back with a clear picture :-)

Thanks again

Regards
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Cross Tab

Postby Tim... » Thu Feb 09, 2012 10:51 am

Hi.

Well, from what I remember, reporting tools like Oracle Reports and Crystal Reports have built in functionality to produce cross tab reports. Are you sure your reporting tool doesn't do some of this work for you? :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Cross Tab

Postby ponic » Thu Feb 09, 2012 11:00 am

Yes trying with a Java based one called BRIT
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Cross Tab

Postby Tim... » Thu Feb 09, 2012 11:08 am

Good luck. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest