Oracle - Return a column per date in a range

Question

Let's say I have Table A: assistance

PersonID        Date           CHECK
123456       2012-01-01          F
213415       2012-01-03          A

PersonID    ArrivalDate       Jan-01    Jan-02    Jan-03    
123456       2012-01-01          F       NULL      NULL         
213415       2012-01-03         NULL     NULL       A

The system is for checks, between 1 to 15 days but no more than that. Any ideas would be very much appreciated.

In "normal" SQL you can do it only if the number of columns is determined beforehand (not after reading the input data), and the column names and their order must also be known beforehand. Otherwise you need dynamic SQL (advanced, and generally not recommended anyway), or you can do it in your reporting application rather than in SQL - the much better place to do such formatting. — , Feb 20 '18 at 22:46

score 0 · Answer 1 · answered Feb 21 '18 at 01:58

you can try this, but I'm not sure if this is what you need,

with inputs_
  as (select 123456 person_id, to_date('2012-01-01', 'YYYY-MM-DD') date_, 'F' check_
       from dual
      union all
      select 213415 person_id, to_date('2012-01-03', 'YYYY-MM-DD'), 'A' check_
        from dual
      UNION ALL
      select 123456 person_id, to_date('2012-01-01', 'YYYY-MM-DD') date_, 'F' check_
       from dual
      union all
      select 213415 person_id, to_date('2012-01-03', 'YYYY-MM-DD'), 'A' check_
        from dual
      union all
      select 123456 person_id, to_date('2012-01-04', 'YYYY-MM-DD') date_, 'F' check_
       from dual
      union all
      select 213415 person_id, to_date('2012-01-05', 'YYYY-MM-DD'), 'A' check_
        from dual
      union all
      select 123456 person_id, to_date('2012-01-02', 'YYYY-MM-DD') date_, 'A' check_
       from dual
      union all
      select 213415 person_id, to_date('2012-01-04', 'YYYY-MM-DD'), 'A' check_
        from dual
      UNION ALL
      select 213415 person_id, to_date('2012-01-02', 'YYYY-MM-DD'), 'F' check_
        from dual)
     select *
       from (select  person_id, date_ arrival_date, check_, TO_CHAR(date_, 'DD-MON') date_
               from inputs_)
              pivot (min(check_) for date_ in ('01-JAN', '02-JAN', '03-JAN', '04-JAN', '05-JAN')
             )
       order by 2;

Output:

Also this is not dynamic, so if you want dynamic pivot, you can see this link, Dynamic pivot in oracle sql

Kaushik Nayak · Answer 2 · 2018-02-21T05:03:38.903

You could construct dynamic date values for PIVOT's IN clause for the range min date to max date using a query. Then open a dynamic cursor with the required arguments in the IN clause of PIVOT. DBMS_SQL.RETURN_RESULT ( 12c and later ) will display the desired result. For older versions, you may refer my answer here to easily display the cursor's output: Display result

DECLARE
  v_instring VARCHAR2 (1000);
  v_cur SYS_REFCURSOR;
BEGIN
WITH dt ( min_t ,max_t ) AS
  ( SELECT MIN(Date_t) ,MAX(Date_t) FROM TableA
   ) ,
  datevalues (date_ch) AS
  (SELECT TO_CHAR(min_t + lvl - 1, 'DD-MON')
  FROM dt CROSS APPLY
    ( SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= max_t - min_t + 1
    )
  )
SELECT LISTAGG(''''
  || date_ch
  || ''' AS "'
  || date_ch, '",') WITHIN GROUP (
ORDER BY date_ch )||'"'
INTO v_instring
FROM
  ( SELECT DISTINCT date_ch FROM datevalues
  );
OPEN v_cur FOR 'select * from (select  PersonID, date_t arrival_date, check_t,
TO_CHAR(date_t, ''DD-MON'') date_t from TableA) pivot ( min(check_t) 
for date_t in ('||v_instring||')) ORDER BY arrival_date';

DBMS_SQL.RETURN_RESULT(v_cur);

END;
/

PERSONID      ARRIVAL_DATE    01-JAN 02-JAN 03-JAN 04-JAN 05-JAN 06-JAN
------------- --------------- ------ ------ ------ ------ ------ ------
123456        01-01-12        F                                        
213415        03-01-12                      A                          
213416        04-01-12                             F                   
345677        06-01-12                                              A

Oracle - Return a column per date in a range

2 Answers2