Oracle 动态枢转日期行，并为每个新日期重复静态列

我们正在尝试在resultset上创建一个报告，其中对于OUC，EMP_FIRST_NAME，姓氏，EIN列的每个组合，
ROSTER_IND和abdence_code和OVERTIME_IND列应显示为与每个出席日期相对应。

我们知道如何动态填充枢轴日期列，但这里的困难是填充
ROSTER_IND和abence_code和OVERTIME_IND列作为第二行，对应于第一行中的每个出席日期。请帮助。


测试用例如下:

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');
复制

我们需要创建一个如下的报告。

    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   
复制

我们还没有动态枢轴，但看看这些潜在的解决方案

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

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