CODEWITHSUNDEEP
sqloracle-databasepivotdynamic-pivot
Matt
Matt

Reputation: 15061

Oracle SQL Dynamic PIVOT Current month, last 6 Months and a total

I have a simple query which returns my base data:

SELECT b.MRID, r.GID, b.VDATE
FROM BAC b
LEFT JOIN BAR r ON b.MRID = r.RID

Which outputs:

MRID           GID  VDATE
John Smith     com  15-Oct-18
John Smith     com  NULL
Joe Bloggs     ad   02-Jun-18
Joe Bloggs     ad   14-Jul-18
Homer Simpson  bil  17-Oct-18
Homer Simpson  bil  NULL
Rick Grimes    zee  12-Nov-18
Rick Grimes    zee  NULL

What I want to output is a Dynamic PIVOT:

MRID           GID  Current Month   Oct-18  Sep-18  Aug-18  Jul-18  Jun-18  May-18  Total
John Smith     com  0               1      0        0       0       0       0       1
Joe Bloggs     ad   0               0      0        0       1       1       0       2
Homer Simpson  bil  0               1      0        0       0       0       0       1
Rick Grimes    zee  1               0      0        0       0       0       0       1

I want this to dynamically change as the months move on with current month and a total.

Upvotes: 0

Views: 1613

Answers (1)

LauDec
LauDec

Reputation: 548

Having your column name change dynamically will be difficult. There are a couple of solutions out there but none of them are really what you want

1) Doing an "pivot XML" with specifying ANY in the list of values you want to pivot ON. But it will give you the result as an XML which is ... not ideal

2) Doing a PIPELINED function accepting a cursor which will create the pivot for you. But it requires creating types and stored procedures

If you accept to change your column names to something Generic like MONTH-1 ... you can easily do it as below : 

CREATE TABLE BAC ( MRID VARCHAR2(30), VDATE DATE );
CREATE TABLE BAR ( RID VARCHAR2(30), GID VARCHAR2(3));

INSERT INTO BAC VALUES ( 'John Smith',TO_DATE('15-Oct-18','DD-MON-YY'));
INSERT INTO BAC VALUES ( 'John Smith',null);
INSERT INTO BAC VALUES ( 'Joe Bloggs',TO_DATE('02-Jun-18','DD-MON-YY'));
INSERT INTO BAC VALUES ( 'Joe Bloggs',TO_DATE('14-Jul-18','DD-MON-YY'));
INSERT INTO BAC VALUES ( 'Homer Simpson',TO_DATE('17-Oct-18','DD-MON-YY'));
INSERT INTO BAC VALUES ( 'Homer Simpson',null);
INSERT INTO BAC VALUES ( 'Rick Grimes',TO_DATE('12-Nov-18','DD-MON-YY'));
INSERT INTO BAC VALUES ( 'Rick Grimes',null);

INSERT INTO BAR VALUES ( 'John Smith','com');
INSERT INTO BAR VALUES ( 'Joe Bloggs','ad');
INSERT INTO BAR VALUES ( 'Homer Simpson','bil');
INSERT INTO BAR VALUES ( 'Rick Grimes','zee');

WITH MONTHS AS 
(
    SELECT ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-LEVEL+1) AS MONTH, DECODE(LEVEL,1,'CURRENT_MONTH','MONTH_MINUS_'||(LEVEL-1)) AS MONTH_NAME FROM DUAL CONNECT BY LEVEL <=7
)
SELECT 
        MRID, 
        GID,
        NVL(CURRENT_MONTH,0) AS CURRENT_MONTH,
        NVL(MONTH_MINUS_1,0) AS MONTH_MINUS_1,
        NVL(MONTH_MINUS_2,0) AS MONTH_MINUS_2,
        NVL(MONTH_MINUS_3,0) AS MONTH_MINUS_3,
        NVL(MONTH_MINUS_4,0) AS MONTH_MINUS_4,
        NVL(MONTH_MINUS_5,0) AS MONTH_MINUS_5,
        NVL(MONTH_MINUS_6,0) AS MONTH_MINUS_6,
        TOTAL 
FROM (
    SELECT 
        DISTINCT B.MRID, R.GID, MONTH_NAME, 
        COUNT(*) OVER (PARTITION BY B.MRID, R.GID, TRUNC(VDATE,'MONTH') ORDER BY VDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CNT, 
        COUNT(*) OVER (PARTITION BY B.MRID, R.GID ORDER BY VDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS TOTAL
    FROM MONTHS M
    JOIN BAC B ON M.MONTH=TRUNC(B.VDATE,'MONTH')
    LEFT JOIN BAR R ON b.MRID = r.RID 
) PIVOT 
( SUM(CNT) 
    FOR MONTH_NAME IN 
        ('CURRENT_MONTH' AS CURRENT_MONTH ,
        'MONTH_MINUS_1' AS MONTH_MINUS_1,
        'MONTH_MINUS_2' AS MONTH_MINUS_2,
        'MONTH_MINUS_3' AS MONTH_MINUS_3,
        'MONTH_MINUS_4' AS MONTH_MINUS_4,
        'MONTH_MINUS_5' AS MONTH_MINUS_5,
        'MONTH_MINUS_6' AS MONTH_MINUS_6)
);

MRID                           GID CURRENT_MONTH MONTH_MINUS_1 MONTH_MINUS_2 MONTH_MINUS_3 MONTH_MINUS_4 MONTH_MINUS_5 MONTH_MINUS_6      TOTAL
------------------------------ --- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ----------
John Smith                     com             0             1             0             0             0             0             0          1
Rick Grimes                    zee             1             0             0             0             0             0             0          1
Homer Simpson                  bil             0             1             0             0             0             0             0          1
Joe Bloggs                     ad              0             0             0             0             1             1             0          2

Upvotes: 2

Related Questions