with t1 as (select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 1, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 2, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 3, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 4, 'YYYYMMDD') as "巡检时间" from dual union all select '设备1' as "检查点", '-1' as "巡检标准", to_char(sysdate - 5, 'YYYYMMDD') as "巡检时间" from dual union all select '设备2' as "检查点", '-1' as "巡检标准", to_char(sysdate - 5, 'YYYYMMDD') as "巡检时间" from dual) select * from t1 pivot(avg("巡检时间") for "巡检时间" in('20180321' as "巡检时间t1", '20180322' as "巡检时间t2", '20180323' as "巡检时间t3", '20180324' as "巡检时间t4", '20180325' as "巡检时间t5"));
select 巡检点,巡检标准,xx1 巡检时间,xx2 值 from t2 unpivot(xx2 FOR xx1 IN (T1,T2....Tn) ) group by 巡检点,巡检标准
with v_base as ( select '设备1' as xjd , '<1' as xjbz, date '2018-02-02' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-03' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-04' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-05' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-06' as dt from dual) select * from (select t.*, row_number() over(order by dt) as rn from v_base t) v1 pivot(max(dt) for rn in(1 as "巡检时间(t1)", 2 as "巡检时间(t2)", 3 as "巡检时间(t3)", 4 as "巡检时间(t4)", 5 as "巡检时间(t5)"))
with v_base as ( select '设备1' as xjd , '<1' as xjbz, date '2018-02-02' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-03' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-04' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-05' as dt from dual union all select '设备1' as xjd , '<1' as xjbz, date '2018-02-06' as dt from dual) select XJD, max(xjbz) as xjbz, max(case when rn = 1 then dt end) as "巡检时间(t1)", max(case when rn = 2 then dt end) as "巡检时间(t2)", max(case when rn = 3 then dt end) as "巡检时间(t3)", max(case when rn = 4 then dt end) as "巡检时间(t4)", max(case when rn = 5 then dt end) as "巡检时间(t5)" from (select t.*, row_number() over(order by dt) as rn from v_base t) v1 group by XJD
参考:https://blog.csdn.net/seandba/article/details/72730657
with t1 as
(select '设备1' as "检查点",
'-1' as "巡检标准",
to_char(sysdate - 1, 'YYYYMMDD') as "巡检时间"
from dual
union all
select '设备1' as "检查点",
'-1' as "巡检标准",
to_char(sysdate - 2, 'YYYYMMDD') as "巡检时间"
from dual
union all
select '设备1' as "检查点",
'-1' as "巡检标准",
to_char(sysdate - 3, 'YYYYMMDD') as "巡检时间"
from dual
union all
select '设备1' as "检查点",
'-1' as "巡检标准",
to_char(sysdate - 4, 'YYYYMMDD') as "巡检时间"
from dual
union all
select '设备1' as "检查点",
'-1' as "巡检标准",
to_char(sysdate - 5, 'YYYYMMDD') as "巡检时间"
from dual
union all
select '设备2' as "检查点",
'-1' as "巡检标准",
to_char(sysdate - 5, 'YYYYMMDD') as "巡检时间"
from dual)
select *
from t1 pivot(avg("巡检时间") for "巡检时间" in('20180321' as "巡检时间t1",
'20180322' as "巡检时间t2",
'20180323' as "巡检时间t3",
'20180324' as "巡检时间t4",
'20180325' as "巡检时间t5"));
from t2
unpivot(xx2 FOR xx1 IN (T1,T2....Tn)
)
group by 巡检点,巡检标准
select '设备1' as xjd , '<1' as xjbz, date '2018-02-02' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-03' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-04' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-05' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-06' as dt from dual)
select *
from (select t.*, row_number() over(order by dt) as rn from v_base t) v1
pivot(max(dt)
for rn in(1 as "巡检时间(t1)",
2 as "巡检时间(t2)",
3 as "巡检时间(t3)",
4 as "巡检时间(t4)",
5 as "巡检时间(t5)"))
select '设备1' as xjd , '<1' as xjbz, date '2018-02-02' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-03' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-04' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-05' as dt from dual union all
select '设备1' as xjd , '<1' as xjbz, date '2018-02-06' as dt from dual)
select
XJD,
max(xjbz) as xjbz,
max(case when rn = 1 then dt end) as "巡检时间(t1)",
max(case when rn = 2 then dt end) as "巡检时间(t2)",
max(case when rn = 3 then dt end) as "巡检时间(t3)",
max(case when rn = 4 then dt end) as "巡检时间(t4)",
max(case when rn = 5 then dt end) as "巡检时间(t5)"
from (select t.*, row_number() over(order by dt) as rn from v_base t) v1
group by XJD