sql - Oracle - 将行转入列
问题描述
我想将以下行结果转换为列。我正在考虑 PIVOT,但我不相信我能达到预期的结果。
原样:
ID_NCM | DESTINATION | START | END
36393 | PE | 01-01-2018 | 10-31-2018
36393 | PE | 11-01-2018 | 12-31-9999
成为:
ID_NCM | DESTINATION | CURRENT_START | CURRENT_END | FUTURE_START | FUTURE_END
36393 | PE | 01-01-2018 | 10-31-2018 | 11-01-2018 | 12-31-9999
我是否在这里遗漏了一些表结构概念,从而无法通过 select 语句实现所需的布局?
谢谢。
解决方案
你可以直接使用grouping by ID_NCM,DESTINATION
withmin
和max
functions as :
with t(ID_NCM,DESTINATION,"START","END") as
(
select 36393,'PE',date'2018-01-01',date'2018-10-31' from dual union all
select 36393,'PE',date'2018-01-11',date'9999-12-31' from dual
)
select ID_NCM,DESTINATION,
min("START") as CURRENT_START,min("END") as CURRENT_END,
max("START") as FUTURE_START,max("END") as FUTURE_END
from t
group by ID_NCM,DESTINATION
ID_NCM DESTINATION CURRENT_START CURRENT_END FUTURE_START FUTURE_END
------ ----------- ------------- ------------ ------------ -----------
36393 PE 01.01.2018 31.10.2018 11.01.2018 31.12.9999
PSSTART
和END
是 中的保留关键字Oracle
,因此用双引号将它们包裹起来。
编辑:由于您的最后评论,您可以通过添加相关子查询来进行更改,如下所示:
with t(ID_NCM,DESTINATION,"START","END",tax_rate) as
(
select 36393,'PE',date'2018-01-01',date'2018-10-31',0.06 from dual union all
select 36393,'PE',date'2018-01-11',date'9999-12-31',0.04 from dual
)
select ID_NCM,DESTINATION,
min("START") as CURRENT_START,min("END") as CURRENT_END,
max("START") as FUTURE_START,max("END") as FUTURE_END,
(select tax_rate from t where "START"=date'2018-01-01' and "END"=date'2018-10-31')
as current_rate,
(select tax_rate from t where "START"=date'2018-01-11' and "END"=date'9999-12-31')
as future_rate
from t
group by ID_NCM,DESTINATION
ID_NCM DEST CURRENT_START CURRENT_END FUTURE_START FUTURE_END CURRENT_RATE FUTURE_RATE
------ ----- ------------- ------------ ------------ ----------- ----------- -----------
36393 PE 01.01.2018 31.10.2018 11.01.2018 31.12.9999 0,06 0,04
推荐阅读
- sql - 在oracle中根据self join更新列
- php - Google Calendar API InvalidArgumentException:“无效代码”
- python - 将深度解析输出转换为数据框
- html - 当文本用 flex 换行到第二行时,无法在图像旁边对齐文本
- java - 从 Firestore Android 按类别名称对 Recyclerview 项目数据进行排序
- python - 无法导入pygame
- ios - VPN 未在 Mac Catalyst 上连接,出现错误,即返回 SecKeychainItemCopyContent 无法检索此项目的内容
- r - 如何将重要性标签添加到具有离散 x 轴值的成对箱线图中?
- r - 如何根据名称列表对我的数据框的行进行子集化?
- android - 使用 AlarmManger 在 android 中安排多个重复通知的问题