How to use oracle SQL statement to a table according to the time line train into the appearance of table 2
Novice one, forget the great spirit guide!!!!! Thank you very much!!!!!
CodePudding user response:
You can use the pivotReference: https://blog.csdn.net/seandba/article/details/72730657
CodePudding user response:
With t1 as
(select '1' equipment as "checkpoint",
'1' as "inspection standard",
To_char (sysdate - 1, 'YYYYMMDD') as "inspection time
"The from dual
Union all
Select '1' equipment as "checkpoint",
'1' as "inspection standard",
To_char (sysdate - 2, 'YYYYMMDD') as "inspection time
"The from dual
Union all
Select '1' equipment as "checkpoint",
'1' as "inspection standard",
To_char (sysdate - 3, 'YYYYMMDD') as "inspection time
"The from dual
Union all
Select '1' equipment as "checkpoint",
'1' as "inspection standard",
To_char (sysdate - 4, 'YYYYMMDD') as "inspection time
"The from dual
Union all
Select '1' equipment as "checkpoint",
'1' as "inspection standard",
To_char (sysdate - 5, 'YYYYMMDD') as "inspection time
"The from dual
Union all
Select '2' equipment as "checkpoint",
'1' as "inspection standard",
To_char (sysdate - 5, 'YYYYMMDD') as "inspection time
"The from dual)
Select *
Inspection time from t1 pivot (avg (" ") for "inspection time" in (' 20180321 'as "inspection time t1",
As "inspection time t2 '20180322',
'20180323' as "inspection time t3",
'20180324' as "inspection time t4",
'20180325' as "inspection time t5"));
CodePudding user response:
Select inspection points, inspection standards, xx1 inspection time, xx2 valueThe from t2
Unpivot (xx2 xx1 (T1, T2 IN... Tn)
)
Group by inspection points, inspection standards
CodePudding user response:
with v_base as (
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-02 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-03 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-04 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-05 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-06 'as dt from dual)
Select *
The from (select t. *, row_number () over (order by dt) as an rn from v_base t) v1
The pivot (Max (dt)
For rn (in 1 as "inspection time (t1),"
2 as "inspection time (t2),"
3 as "inspection time (t3),"
4 as "inspection time (t4),"
5 the as "inspection time (t5)"))
CodePudding user response:
With v_base as (
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-02 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-03 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-04 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-05 'as dt from dual union all
Select '1' equipment as XJD, '& lt; 1 'as XJBZ, the date' 2018-02-06 'as dt from dual)
Select
XJD,
Max (XJBZ) as XJBZ,
Max (case when an rn=1 then dt end) as "inspection time (t1),"
Max (case when an rn=2 then dt end) as "inspection time (t2),"
Max (case when an rn=3 then dt end) as "inspection time (t3),"
Max (case when an rn=4 then dt end) as "inspection time (t4),"
Max (case when an rn=5 then dt end) as "inspection time (t5)"
The from (select t. *, row_number () over (order by dt) as an rn from v_base t) v1
Group by XJD