甲骨文 11g 旋转结果表 90 度

Question

我需要将结果集旋转 90 度。 我一直在使用枢轴和取消枢轴，但到目前为止还没有设法让任何东西工作。 结果集如下所示：

2018-07-03  TUE 20:00   12:15   465 (null)
2018-07-02  MON 20:00   20:05   -5  FAIL
2018-06-29  FRI 19:30   19:04   26  (null)
2018-06-28  THU 20:00   19:07   53  (null)
2018-06-27  WED 20:00   19:37   23  (null)

我需要的是能够将整个结果集旋转 90 度，以便第 1 列(日期(成为列标题，数据的重置落在这些列下，如下所示：

2018-06-27  2018-06-28  2018-06-29  2018-07-02  2018-07-03
WED         THU         FRI         MON         TUE
20:00       20:00       19:30       20:00       20:00
19:37       19:07       19:04       20:05       12:15
23          53          56          -5          465
(null)      (null)      (null)      FAIL        (null)

是否有其他功能使这成为可能，或者可能是我尚未弄清楚的透视/取消透视方法？

Answer 1

你可以生成这样的逻辑，比如使用这个 SQL

select vl as date1, myCol as myCol1
from 
(
select to_char(myDate,'yyyy-mm-dd') as mD, 
to_char(myDate,'DY') as Dy, 
to_char(myVal) as mVl, t.* from tab t
where to_char(mydate,'yyyy-mm-dd') = <a_date_parameter>
)
unpivot (vl for myCol in (mD,Dy,myHourStart, myHourEnd,mVl,status))

由unpivot概念的贡献为：

create table tab( myDate date, myHourStart varchar2(5), myHourEnd varchar2(5), myVal int, status varchar2(15) );
insert all 
into tab values( date'2018-07-03','20:00','12:15', 465, null )
into tab values( date'2018-07-02','20:00','20:05', -5, 'FAIL' )
into tab values( date'2018-06-29','19:30','19:04', 26,  null )
into tab values( date'2018-06-28','20:00','19:07', 53, null )
into tab values( date'2018-06-27','20:00','19:37', 23,  null ) 
select * from dual;
select date1, date2, date3, date4, date5 from
(  select vl as date1, myCol as myCol1
from 
(
select to_char(myDate,'yyyy-mm-dd') as mD, 
to_char(myDate,'DY') as Dy, 
to_char(myVal) as mVl, t.* from tab t
where to_char(mydate,'yyyy-mm-dd') = '2018-06-27'
)
unpivot (vl for myCol in (mD,Dy,myHourStart, myHourEnd,mVl,status)) 
) q1 join
(  select vl as date2, myCol as myCol2
from 
(
select to_char(myDate,'yyyy-mm-dd') as mD, 
to_char(myDate,'DY') as Dy,
to_char(myVal) as mVl, t.* from tab t
where to_char(mydate,'yyyy-mm-dd') = '2018-06-28'
)
unpivot (vl for myCol in (mD,Dy,myHourStart, myHourEnd,mVl,status)) 
) q2 on ( q1.myCol1 = q2.myCol2  )  
join
(  select vl as date3, myCol as myCol3
from 
(
select to_char(myDate,'yyyy-mm-dd') as mD, 
to_char(myDate,'DY') as Dy,
to_char(myVal) as mVl, t.* from tab t
where to_char(mydate,'yyyy-mm-dd') = '2018-06-29'
)
unpivot (vl for myCol in (mD,Dy,myHourStart, myHourEnd,mVl,status)) 
) q3 on ( q1.myCol1 = q3.myCol3  ) 
join
(  select vl as date4, myCol as myCol4
from 
(
select to_char(myDate,'yyyy-mm-dd') as mD, 
to_char(myDate,'DY') as Dy,
to_char(myVal) as mVl, t.* from tab t
where to_char(mydate,'yyyy-mm-dd') = '2018-07-02'
)
unpivot (vl for myCol in (mD,Dy,myHourStart, myHourEnd,mVl,status)) 
) q4 on ( q1.myCol1 = q4.myCol4  )            
join
(  select vl as date5, myCol as myCol5
from 
(
select to_char(myDate,'yyyy-mm-dd') as mD, 
to_char(myDate,'DY') as Dy,
to_char(myVal) as mVl, t.* from tab t
where to_char(mydate,'yyyy-mm-dd') = '2018-07-03'
)
unpivot (vl for myCol in (mD,Dy,myHourStart, myHourEnd,mVl,status)) 
) q5 on ( q1.myCol1 = q5.myCol5  );

SQL 小提琴演示