17,078
社区成员
发帖
与我相关
我的任务
分享
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
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)"))