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