时间戳

首页 > 解决方案 > 具有超过 2 个结果列的透视/取消透视 oracle sql 查询

sql - 具有超过 2 个结果列的透视/取消透视 oracle sql 查询

问题描述

我有一个查询，其连续输出如下：

Math MathStartDate  Science ScienceStartDate    Comp    CompStartDate   Hist    HistStartDate
12      11/12/2020  13      11/02/2020          6       11/01/2020      45      11/01/2020

我希望结果看起来像这样

Code    Value1  Value2
Math    12      11/12/2020
Science 13      11/02/2020
Comp    6       11/01/2020
Hist    45      11/01/2020

为了得到像上面这样的结果，我使用 unpivot 如下：

select * from (
select 
Math,
MathStartDate,
Science,
ScienceStartDate,
Comp,
CompStartDate,
Hist,
HistStartDate
from subjects vw, students s
where s.id = vw.id 
and id = 56
)
UNPIVOT INCLUDE NULLS(value1 FOR code in(Math,
Science,
Comp,
Hist));

这个输出看起来像，

MathStartDate ScienceStartDate CompStartDate HistStartDate code     value1
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Math    12  
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Science 13
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Comp    6
11/12/2020      11/02/2020      11/01/2020      11/01/2020  Hist    45

如何让日期对齐以便获得所需的输出？

标签： sqloracleoracle11gpivotunpivot

解决方案

您可以取消透视多个列：

...
UNPIVOT INCLUDE NULLS (
  (value1, startdate)
  FOR code in (
    (Math, MathStartDate) as 'Math',
    (Science, ScienceStartDate) as 'Science',
    (Comp, CompStartDate) as 'Comp',
    (Hist, HistStartDate) as 'Hist'
  )
);

CODE    | VALUE1 | STARTDATE
------- | ------ | ---------
Math    |     12 | 12-NOV-20
Science |     13 | 02-NOV-20
Comp    |      6 | 01-NOV-20
Hist    |     45 | 01-NOV-20

db<>小提琴

推荐阅读