SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PIVOT returns NULL when duplicate date values used

ronald_2017Jul 13 2025

Hello All,

When I use redundant PIVOT values with different aliases, the result shows NULL for the first redundant columns. However, if I remove the last PIVOT value, it works as expected.

Note that c1 and c3 are the same, and c2 and c12 are the same.

Do you have any idea why it behaves this way?

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> with
  2  q1 as (
  3      select * from (
  4          select trunc(sysdate) - level as dt, level as cnt
  5          from dual
  6          connect by level <= 18
  7          union all
  8          select to_date('03/07/2025', 'dd/mm/yyyy'), 5 from dual
  9      )
 10  )
 11  select * from (
 12      select * from q1
 13  )
 14  pivot (
 15      max(cnt) as price for dt in (
 16          to_date('03/07/2025', 'dd/mm/yyyy') as c1,
 17          to_date('02/07/2025', 'dd/mm/yyyy') as c2,
 18          to_date('03/07/2025', 'dd/mm/yyyy') as c3,
 19          to_date('04/07/2025', 'dd/mm/yyyy') as c4,
 20          to_date('05/07/2025', 'dd/mm/yyyy') as c5,
 21          to_date('07/07/2025', 'dd/mm/yyyy') as c6,
 22          to_date('08/07/2025', 'dd/mm/yyyy') as c7,
 23          to_date('06/07/2025', 'dd/mm/yyyy') as c8,
 24          to_date('09/07/2025', 'dd/mm/yyyy') as c9,
 25          to_date('10/07/2025', 'dd/mm/yyyy') as c10,
 26          to_date('11/07/2025', 'dd/mm/yyyy') as c11,
 27          to_date('02/07/2025', 'dd/mm/yyyy') as c12,
 28          to_date('01/07/2025', 'dd/mm/yyyy') as c13,
 29          to_date('30/06/2025', 'dd/mm/yyyy') as c14,
 30          to_date('29/06/2025', 'dd/mm/yyyy') as c15,
 31          to_date('28/06/2025', 'dd/mm/yyyy') as c16,
 32          to_date('27/06/2025', 'dd/mm/yyyy') as c17,
 33          to_date('26/06/2025', 'dd/mm/yyyy') as c18,
 34          to_date('25/06/2025', 'dd/mm/yyyy') as c19,
 35          to_date('24/06/2025', 'dd/mm/yyyy') as c20
 36      )
 37  );

C1_PRICE C2_PRICE C3_PRICE C4_PRICE C5_PRICE C6_PRICE C7_PRICE C8_PRICE C9_PRICE C10_PRICE C11_PRICE C12_PRICE C13_PRICE C14_PRICE C15_PRICE C16_PRICE C17_PRICE C18_PRICE C19_PRICE C20_PRICE
-------- -------- -------- -------- -------- -------- -------- -------- -------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
                         9        8        7        5        4        6        3         2         1        10        11        12        13        14        15        16        17        18

SQL> with
  2  q1 as (
  3      select * from (
  4          select trunc(sysdate) - level as dt, level as cnt
  5          from dual
  6          connect by level <= 18
  7          union all
  8          select to_date('03/07/2025', 'dd/mm/yyyy'), 5 from dual
  9      )
 10  )
 11  select * from (
 12      select * from q1
 13  )
 14  pivot (
 15      max(cnt) as price for dt in (
 16          to_date('03/07/2025', 'dd/mm/yyyy') as c1,
 17          to_date('02/07/2025', 'dd/mm/yyyy') as c2,
 18          to_date('03/07/2025', 'dd/mm/yyyy') as c3,
 19          to_date('04/07/2025', 'dd/mm/yyyy') as c4,
 20          to_date('05/07/2025', 'dd/mm/yyyy') as c5,
 21          to_date('07/07/2025', 'dd/mm/yyyy') as c6,
 22          to_date('08/07/2025', 'dd/mm/yyyy') as c7,
 23          to_date('06/07/2025', 'dd/mm/yyyy') as c8,
 24          to_date('09/07/2025', 'dd/mm/yyyy') as c9,
 25          to_date('10/07/2025', 'dd/mm/yyyy') as c10,
 26          to_date('11/07/2025', 'dd/mm/yyyy') as c11,
 27          to_date('02/07/2025', 'dd/mm/yyyy') as c12,
 28          to_date('01/07/2025', 'dd/mm/yyyy') as c13,
 29          to_date('30/06/2025', 'dd/mm/yyyy') as c14,
 30          to_date('29/06/2025', 'dd/mm/yyyy') as c15,
 31          to_date('28/06/2025', 'dd/mm/yyyy') as c16,
 32          to_date('27/06/2025', 'dd/mm/yyyy') as c17,
 33          to_date('26/06/2025', 'dd/mm/yyyy') as c18,
 34          to_date('25/06/2025', 'dd/mm/yyyy') as c19--,
 35          --to_date('24/06/2025', 'dd/mm/yyyy') as c20
 36      )
 37  );

C1_PRICE C2_PRICE C3_PRICE C4_PRICE C5_PRICE C6_PRICE C7_PRICE C8_PRICE C9_PRICE C10_PRICE C11_PRICE C12_PRICE C13_PRICE C14_PRICE C15_PRICE C16_PRICE C17_PRICE C18_PRICE C19_PRICE
-------- -------- -------- -------- -------- -------- -------- -------- -------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
       9       10        9        8        7        5        4        6        3         2         1        10        11        12        13        14        15        16        17

Thanks in advance

Added on Jul 13 2025

5 comments

183 views