基于数据的Oracle数据透视帮助

Question

我正在尝试使用oracle pivot函数以以下格式显示数据。我试着用我发现的stackoverflow的例子，但我无法实现我想要的。

With  t as
(
select 1335 as emp_id, 'ADD Insurance New' as suuid, sysdate- 10 as startdate, null as enddate from dual
union all
select 1335 as emp_id, 'HS' as suuid, sysdate- 30 as startdate, null as enddate from dual
union all
select 1335 as emp_id, 'ADD Ins' as suuid, sysdate- 30 as startdate, Sysdate - 10 as enddate from dual
) 
select * from t

输出：

+--------+-------------------+-------------------+---------+-------------------+
| EMP_ID |      SUUID_1      | SUUID_1_STARTDATE | SUUID_2 | SUUID_2_STARTDATE |
+--------+-------------------+-------------------+---------+-------------------+
|   1335 | ADD Insurance New | 10/5/2020 15:52   | HS      | 9/15/2020 15:52   |
+--------+-------------------+-------------------+---------+-------------------+

有人能建议如何使用SQLPivot来获得这种格式吗？

Answer 1

您可以使用条件聚合。有不止一种方法可以理解你的问题，但有一种方法适用于你的样本数据：

select emp_id,
max(case when rn = 1 then suuid end) suuid_1,
max(case when rn = 1 then startdate end) suid_1_startdate,
max(case when rn = 2 then suuid end) suuid_2,
max(case when rn = 2 then startdate end) suid_2_startdate
from (
select t.*, row_number() over(partition by emp_id order by startdate desc) rn
from t
where enddate is null
) t
group by emp_id

DB Fiddle上的演示：

EMP_ID|SUUID_1|SUID_1_STARTDATE|SUUID_2|SUID_2_STARTDATE-----：|：----------------|：---------------|：-----|：---------------1335 |添加新保险| 2020年10月5日| HS | 2020年9月15日

Answer 2

您可以使用PIVOT:

With  t ( emp_id, suuid, startdate, enddate ) as
(
select 1335, 'ADD Insurance New', sysdate- 10, null         from dual union all
select 1335, 'HS',                sysdate- 30, null         from dual union all
select 1335, 'ADD Ins',           sysdate- 30, Sysdate - 10 from dual
)
SELECT emp_id,
"1_SUUID" AS suuid1,
"1_STARTDATE" AS suuid_startdate1,
"2_SUUID" AS suuid2,
"2_STARTDATE" AS suuid_startdate2
FROM   (
SELECT t.*,
ROW_NUMBER() OVER ( ORDER BY startdate DESC, enddate DESC NULLS FIRST )
AS rn
FROM   t
)
PIVOT (
MAX( suuid ) AS suuid,
MAX( startdate ) AS startdate,
MAX( enddate ) AS enddate
FOR rn IN ( 1, 2 )
)

输出：

EMP_ID|SUUID1|SUUID_STARTDATE1|SUUID2|SUUID-STARTDATE2-----：|：----------------|：---------------|：-----|：---------------1335|ADD Insurance New | 2020年10月5日| HS | 2020年9月15日

db<gt；小提琴这里