I’m using Oracle 21C. I have a query that select from 3 tables and works fine with “hard-coded” dates.

Select * from 
(   Select
      vc.last_name
    , vc.first_name  
    , va.attendance_type
    , ve.event_date 
    From
      VOL_CONTACT vc
    , VOL_ATTENDANCE va
    , VOL_EVENT ve
    Where
      va.contact_fkey = vc.prim_key
    And va.event_fkey = ve.prim_key
)
    pivot
    ( max(attendance_type)
      for event_date in ('30-mar-2023','18-apr-2023')
    )

However, The dates need to be dynamic.
I tried to use PIVOT XML with a sub-query. The last four lines were changed to:

pivot xml
( max(attendance_type)
 for event_date in (select distinct to_char(event_date, 'dd-mon-yy') from vol_event)
)

. They show the LAST_NAME and FIRST_NAME values fine. However the column heading of the third column is “EVENT_DATE_XML” and the value of that column for every row is “(XMLTYPE)”. How do I make PIVOT XML work with a sub-query?