Cross tab query in Oracle

[delorfra]

Any instruction to create a cross tab query similar to those in Access ie TRANSFORM/PIVOT commands ?

Thanks

 

[sky1]

Give me an example of a TRANSFORM/PIVOT and maybe I can help you out.

 

[carp]

This is typically done in Oracle with the DECODE function:

SELECT employee_name,
SUM(DECODE(to_char(workday,'MON'),'JAN',1,0) "January Days", SUM(DECODE(to_char(workday,'MON'),'FEB',1,0) "February Days", ....,
SUM(DECODE(to_char(workday,'MON'),'DEC',1,0) "December Days"
FROM work_roster
GROUP BY employee_name;

 

[Ahliana]

I just finished having to create this for a report. In my case, I knew that there were 20 status codes, so I hardcoded for that. I am getting the date and time of the first time the item had each status (so they can see how long it took to go from one status to the next, although many numbers can be skipped).

If you do not know the columns, and therefore need to generate them on the fly, there is a good post from TomSark on Dec 29, 2000 regarding this (sorry, I don't have the thread # handy). He discusses creating a table and then altering it based on the results of your query.

My code below pulls the date of status 1 from a date field, and the time (which was created as a character field LONG before I got here!) from a character field. I am not summing the data, so I do not use the SUM or GROUP BY. I will then query this table to fill these into another table that I have built with lots of other data, for the purpose of reporting.

I hope this is of help to you.

SELECT N_RFSTRACK,
        DECODE(N_STATUS,1,D_STATUSDATE,NULL) STATUS_1_DATE,
        DECODE(N_STATUS,1,C_STATUSTIME,NULL) STATUS_1_TIME,
        DECODE(N_STATUS,2,D_STATUSDATE,NULL) STATUS_2_DATE,
        DECODE(N_STATUS,2,C_STATUSTIME,NULL) STATUS_2_TIME,
.
.
.
        DECODE(N_STATUS,20,D_STATUSDATE,NULL) STATUS_20_DATE,
        DECODE(N_STATUS,20,C_STATUSTIME,NULL) STATUS_20_TIME
FROM
(
SELECT N_RFSTRACK, MIN(D_UPDATEDATE) D_STATUSDATE, MIN(C_UPDATETIME) C_STATUSTIME, N_STATUS
FROM
    RFSUPDATES,
    RFSSTATUS
WHERE
    RFSSTATUS.N_ID = RFSUPDATES.N_STATUS
GROUP BY N_RFSTRACK, N_STATUS
)

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach

 

[Ahliana]

Oops! I should have tested more thoroughly. My previous post gave a line for each record. To get a true crosstab, I had to convert dates and times (that were characters) to numbers, sum them, test for 0's, then convert them back to what they were. Then I get one line for each item, instead of for each item/datetime combo. Corrected code follows:

SELECT N_RFSTRACK,
        DECODE(SUM(DECODE(N_STATUS,1,STATUSDATE,0)),0,NULL,TO_DATE(SUM(DECODE(N_STATUS,1,STATUSDATE,0)),'YYYYMMDD')) STATUS_1_DATE,
        DECODE(SUM(DECODE(N_STATUS,1,STATUSTIME,0)),0,NULL,SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,1,STATUSTIME,0))),1,2) || ':' || SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,1,STATUSTIME,0))),3,2)) STATUS_1_TIME,
        DECODE(SUM(DECODE(N_STATUS,2,STATUSDATE,0)),0,NULL,TO_DATE(SUM(DECODE(N_STATUS,2,STATUSDATE,0)),'YYYYMMDD')) STATUS_2_DATE,
        DECODE(SUM(DECODE(N_STATUS,2,STATUSTIME,0)),0,NULL,SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,2,STATUSTIME,0))),1,2) || ':' || SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,2,STATUSTIME,0))),3,2)) STATUS_2_TIME,
.
.
.
        DECODE(SUM(DECODE(N_STATUS,20,STATUSDATE,0)),0,NULL,TO_DATE(SUM(DECODE(N_STATUS,20,STATUSDATE,0)),'YYYYMMDD')) STATUS_20_DATE,
        DECODE(SUM(DECODE(N_STATUS,20,STATUSTIME,0)),0,NULL,SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,20,STATUSTIME,0))),1,2) || ':' || SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,20,STATUSTIME,0))),3,2)) STATUS_20_TIME

FROM
(

      SELECT
          N_RFSTRACK,
          TO_NUMBER(TO_CHAR(D_STATUSDATE,'YYYYMMDD')) STATUSDATE,
          TO_NUMBER(SUBSTR(C_STATUSTIME,1,2)||SUBSTR(C_STATUSTIME,4,2)) STATUSTIME,
          N_STATUS
      FROM
      (    
        SELECT
            N_RFSTRACK,
            MIN(D_UPDATEDATE) D_STATUSDATE,
            MIN(C_UPDATETIME) C_STATUSTIME,
            N_STATUS
        FROM
            RFSUPDATES,
            RFSSTATUS
        WHERE
            RFSSTATUS.N_ID = RFSUPDATES.N_STATUS
        GROUP BY N_RFSTRACK, N_STATUS
        )
)
GROUP BY N_RFSTRACK

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach