Cross Tab

[ponic]

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

[Tim...]

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...

[ponic]

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

[Tim...]

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...

[ponic]

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

[Tim...]

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...

[ponic]

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

[Tim...]

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...

[ponic]

Yes trying with a Java based one called BRIT

[Tim...]

Good luck.