CODEWITHSUNDEEP
sqloracle-databasedatetimepivot
Rag Pudi
Rag Pudi

Reputation: 43

Oracle Pivot Help based on Data

I am trying use a oracle pivot function to display the data in below format. I have tried to use examples I found stackoverflow, but I am unable to achieve what I am looking.

    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

output:

+--------+-------------------+-------------------+---------+-------------------+
| 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   |
+--------+-------------------+-------------------+---------+-------------------+

Can anyone suggest to how to use SQL Pivot to get this format?

Upvotes: 0

Views: 56

Answers (2)

MT0
MT0

Reputation: 167991

You can do it with 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 )
)

Outputs:

EMP_ID | SUUID1            | SUUID_STARTDATE1 | SUUID2 | SUUID_STARTDATE2
-----: | :---------------- | :--------------- | :----- | :---------------
  1335 | ADD Insurance New | 05-OCT-20        | HS     | 15-SEP-20

db<>fiddle here

Upvotes: 1

GMB
GMB

Reputation: 222482

You can use conditional aggregation. There is more than one way to understand your question, but one approach that would work for your sample data is:

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

Demo on DB Fiddle:

EMP_ID | SUUID_1           | SUID_1_STARTDATE | SUUID_2 | SUID_2_STARTDATE
-----: | :---------------- | :--------------- | :------ | :---------------
  1335 | ADD Insurance New | 05-OCT-20        | HS      | 15-SEP-20

Upvotes: 1

Related Questions