I have this query for Employee's fingerprints

SELECT 
bp.Name
vahruae_date date,
to_char(vahruae_timeofattendance, 'hh:mi') Time
FROM vahruae_hr_empattendancelog empl
INNER JOIN c_bpartner bp ON empl.vahruae_enroll_id = bp.enroll_id

And the value displays as follows

Name              Date              Time
-----------------------------------------
John           01-SEP-2022          1:00
John           01-SEP-2022          9:00
John           01-SEP-2022          13:00
John           01-SEP-2022          16:00
John           02-SEP-2022          1:00
John           02-SEP-2022          6:00
John           04-SEP-2022          5:00
.                  .
.                  .
John           31-SEP-2022          4:30
John           31-SEP-2022          7:00
Ali            01-SEP-2022          10:00
Ali            04-SEP-2022          5:00
.                  .
.                  .

I want to display all times in one date row, like this

Name              Date              Time1           Time2            Time3          Time4
-----------------------------------------------------------------------------------------------
John           01-SEP-2022          1:00            9:00             13:00          16:00
John           02-SEP-2022          1:00            6:00             (null)         (null)
John           04-SEP-2022          5:00           (null)            (null)         (null)
John           31-SEP-2022          4:30            7:00             (null)         (null)
Ali            01-SEP-2022          10:00          (null)            (null)         (null)
Ali            05-SEP-2022          5:00           (null)            (null)         (null)

I'm not very good with Pivot, but I know I cannot use a subquery inside the Pivot IN Clause.
How can I accomplish that?

CodePudding user response：

Use the ROW_NUMBER analytic function to index the times within a sub-query and then PIVOT using that:

SELECT name,
       dt,
       time1,
       time2,
       time3,
       time4
FROM   (
  SELECT empl.vahruae_enroll_id,
         bp.Name,
         vahruae_date AS dt,
         to_char(vahruae_timeofattendance, 'hh:mi') AS Time,
         ROW_NUMBER() OVER (
           PARTITION BY empl.vahruae_enroll_id,
                        vahruae_date,
                        TRUNC(vahruae_timeofattendance)
           ORDER BY     vahruae_timeofattendance
         ) AS rn
  FROM   vahruae_hr_empattendancelog empl
         INNER JOIN c_bpartner bp
         ON empl.vahruae_enroll_id = bp.enroll_id
)
PIVOT (
  MAX(time)
  FOR rn IN (1 AS time1, 2 AS time2, 3 AS time3, 4 AS time4)
)

CodePudding user response：

We need to number the hours by date and name using row_number() and then pivoting.

select  *
from 
(  
select  "Name"                                                                                                     as "Name"
       ,to_char("Date", 'hh24:mi')                                                                                 as "Time"
       ,to_date(to_char("Date", 'DD-mon-YYYY'))                                                                    as "Date"
       ,row_number() over(partition by "Name", to_char("Date", 'DD-mon-YYYY') order by to_char("Date", 'hh24:mi')) as rn

from   t
) t
pivot(max("Time") for rn in('1' as Time1, '2' as Time2, '3' as Time3, '4' as Time4)) p
order by "Name" desc, "Date"

Fiddle