Skip to Main Content
  • Questions

  • Pivoting Date rows dynamically and repeating static columns for every new date

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prabhat.

Asked: July 31, 2019 - 2:10 am UTC

Last updated: August 07, 2019 - 10:26 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

We are trying to create a report on a resultset where For each combination of OUC,EMP_FIRST_NAME,SURNAME,EIN columns,
ROSTER_IND and ABSENCE_CODE and OVERTIME_IND columns should appear corresponsding to every ATTENDANCE_DATE.

We know how to populate pivoted date columns dynamically, but the difficulty here is to populate
ROSTER_IND and ABSENCE_CODE and OVERTIME_IND columns as 2nd row corresponding to each ATTENDANCE_DATE in 1st row. Kindly help.


Test Case is as below:

CREATE TABLE TEST_PIVOT 
   (OUC    VARCHAR2(6), 
 EMP_FIRST_NAME VARCHAR2(50), 
 SURNAME   VARCHAR2(50), 
 EIN    NUMBER(9) NOT NULL, 
 ATTENDANCE_DATE DATE, 
 ROSTER_IND   VARCHAR2(1), 
 ABSENCE_CODE  VARCHAR2(2), 
 OVERTIME_IND  VARCHAR2(1)
 );
   
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('18-JUL-19','DD-MON-YY'),NULL,'LL',NULL);
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('19-JUL-19','DD-MON-YY'),NULL,'LL',NULL);
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('20-JUL-19','DD-MON-YY'),'R',NULL,NULL);
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('21-JUL-19','DD-MON-YY'),'R',NULL,NULL);
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('22-JUL-19','DD-MON-YY'),NULL,NULL,'W');
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('23-JUL-19','DD-MON-YY'),NULL,NULL,'W');
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('24-JUL-19','DD-MON-YY'),NULL,'GL','M');
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('25-JUL-19','DD-MON-YY'),'R','GL','M');
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('26-JUL-19','DD-MON-YY'),NULL,NULL,NULL);
insert into TEST_PIVOT values('ABC123','David','Nick',1234,to_date('27-JUL-19','DD-MON-YY'),'R',NULL,'W');

insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('18-JUL-19','DD-MON-YY'),NULL,'LL',NULL);
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('19-JUL-19','DD-MON-YY'),'R',NULL,NULL);
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('20-JUL-19','DD-MON-YY'),NULL,'LL',NULL);
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('21-JUL-19','DD-MON-YY'),'R',NULL,NULL);
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('22-JUL-19','DD-MON-YY'),NULL,NULL,'W');
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('23-JUL-19','DD-MON-YY'),NULL,NULL,'W');
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('24-JUL-19','DD-MON-YY'),NULL,'GL','M');
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('25-JUL-19','DD-MON-YY'),'R','GL','M');
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('26-JUL-19','DD-MON-YY'),NULL,NULL,NULL);
insert into TEST_PIVOT values('ABC789','Jorge','Matt',7890,to_date('27-JUL-19','DD-MON-YY'),'R',NULL,'W');

insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('18-JUL-19','DD-MON-YY'),NULL,'LL',NULL);
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('19-JUL-19','DD-MON-YY'),NULL,NULL,NULL);
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('20-JUL-19','DD-MON-YY'),'R',NULL,NULL);
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('21-JUL-19','DD-MON-YY'),'R',NULL,NULL);
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('22-JUL-19','DD-MON-YY'),NULL,NULL,'W');
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('23-JUL-19','DD-MON-YY'),NULL,NULL,'W');
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('24-JUL-19','DD-MON-YY'),NULL,'GL','M');
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('25-JUL-19','DD-MON-YY'),'R','GL','M');
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('26-JUL-19','DD-MON-YY'),NULL,NULL,NULL);
insert into TEST_PIVOT values('ABC456','Tailor','Henry',4567,to_date('27-JUL-19','DD-MON-YY'),'R',NULL,'W');



We Need to create a report as below.


    18-Jul-19 18-Jul-19 18-Jul-19 19-Jul-19 19-Jul-19 19-Jul-19 20-Jul-19 20-Jul-19 20-Jul-19 21-Jul-19 21-Jul-19 21-Jul-19 ...
    ROSTER_IND ABSENCE_CODE OVERTIME_IND ROSTER_IND ABSENCE_CODE OVERTIME_IND ROSTER_IND ABSENCE_CODE OVERTIME_IND ROSTER_IND ABSENCE_CODE OVERTIME_IND ...
ABC123 David Nick 1234  LL   LL  R   R   
ABC789 Jorge Matt 7890  LL  R    LL  R   
ABC456 Tailor Henry 4567  LL     R   R

and Connor said...

We don't have dynamic pivot natively yet, but check out these potential solutions

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

https://stewashton.wordpress.com/2018/05/28/generic-pivot-function/



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.