怎么用oracle sql 陳述句將表一 根據時間 行專列 成表二的樣子
菜鳥一枚。忘大神指導！！感激不盡！！
uj5u.com熱心網友回復：
可以用這個 pivot參考：https://blog.csdn.net/seandba/article/details/72730657
uj5u.com熱心網友回復：
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"));
uj5u.com熱心網友回復：
select 巡檢點，巡檢標準，xx1 巡檢時間,xx2 值from t2
unpivot(xx2 FOR xx1 IN (T1,T2....Tn)
)
group by 巡檢點，巡檢標準
uj5u.com熱心網友回復：
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)"))
uj5u.com熱心網友回復：
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://www.uj5u.com/shujuku/65132.html
標籤：開發