title: PIVOT function with String columns return wrong output result
body: <p>I have two tables named </p>
  
  <p><strong>class_record_hist</strong> which will store the attendance entry of a class for a particular date </p>
  
  <p><strong>student_record_hist</strong> which will store the list of students attendance for the particular class.Here the transaction_id will be the primary key for both tables</p>
  
  <p>MY Data looks like this </p>
  
  <pre><code>Working_date                     STUDENT_NAME   STUDENT_STATUS  ATTENDANCE_CODE   TEACHER_UPDATE   TRANSACTION_ID 
  -------------                    ------------   --------------  ---------------   --------------   --------------
  
  2017-11-10 10:00:00               JAMES          ABSENT            2344               AB               23453
  
  2017-11-10 10:00:00               PETER          ABSENT            2344               AB               23453
  
  2017-11-10 10:00:00               MURPHY         PRESENT           1001               PR               23453
  
  2017-11-10 10:00:00               MICA           PRESENT           1001               PR               23453
  
  2017-11-10 10:00:00               STELLA         PRESENT           1001               PR               23453
  
  2017-11-10 10:00:00               STEPHEN        PRESENT           1001               PR               23453
  
  2017-11-10 10:00:00               TRACY          PRESENT           1001               PR               23453
  
  2017-11-10 14:00:00               JAMES          PRESENT           1001               PR               23454
  
  2017-11-10 14:00:00               PETER          PRESENT           1001               PR               23454
  
  2017-11-10 14:00:00               MURPHY         PRESENT           1001               PR               23454
  
  2017-11-10 14:00:00               MICA           PRESENT           1001               PR               23454
  
  2017-11-10 14:00:00               STELLA         PRESENT           1001               PR               23454
  
  2017-11-10 14:00:00               STEPHEN        PRESENT           1001               PR               23454
  
  2017-11-10 14:00:00               TRACY          ABSENT            2344               AB               23454
  
  2017-11-11 11:24:00               JAMES          PRESENT           1001               PR               23454
  
  2017-11-11 11:24:00               PETER          PRESENT           1001               PR               23454
  
  2017-11-11 11:24:00               MURPHY         PRESENT           1001               PR               23454
  
  2017-11-11 11:24:00               MICA           PRESENT           1001               PR               23454
  
  2017-11-11 11:24:00               STELLA         PRESENT           1001               PR               23454
  
  2017-11-11 11:24:00               STEPHEN        PRESENT           1001               PR               23454
  
  2017-11-11 11:24:00               TRACY          PRESENT           1001               PR               23454
  
  2017-11-11 14:24:00               JAMES          ABSENT            2344               AB               23454
  
  2017-11-11 14:24:00               PETER          ABSENT            2344               AB               23454
  
  2017-11-11 14:24:00               MURPHY         ABSENT            2344               AB               23454
  
  2017-11-11 14:24:00               MICA           ABSENT            2344               AB               23454
  
  2017-11-11 14:24:00               STELLA         ABSENT            2344               AB               23454
  
  2017-11-11 14:24:00               STEPHEN        PRESENT           1001               PR               23454
  
  2017-11-11 14:24:00               TRACY          PRESENT           1001               PR               23454
  </code></pre>
  
  <p>I will be generating a report based on this records </p>
  
  <p>And this is my query like </p>
  
  <pre><code>select * from 
  ( 
  select student_name, student_status,attendance_code, 
  working_date,class_id from 
  ( 
  select to_char(a.date_tm,'yyyy-mm-dd hh24:mi:ss')  AS working_date, 
  b.student_name,b.student_status,b.attendance_code, 
  b.teacher_update,a.transaction_id 
  from class_record_hist a,student_record_hist b 
  where a.school        = 'DON BOSCO'
  and a.building      = 'A1'
  and a.class_id   = 'DB-3452' 
  and a.date_tm &gt;= to_date('2017-11-10 06:00:00','yyyy-mm-dd hh24:mi:ss') 
  and a.date_tm  &lt;= to_date('2017-11-11 18:00:00','yyyy-mm-dd hh24:mi:ss') 
  and a.transaction_id      = b.transaction_id 
  order by working_date desc,b.student_name asc
  )
  ) 
  PIVOT 
  ( 
  max(attendance_code) as code, 
  max(student_status) as status
  for student_name in ('JAMES','PETER','MURPHY','MICA','STELLA','STEPHEN','TRACY');
  ) ; 
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>WORKING_DATE        CLASS_ID  JAMES_STATUS   JAMES_CODE  PETER_STATUS  PETER_CODE  MURPHY_STATUS  MURPHY_CODE  MICA_STATUS  MICA_CODE   STELLA_STATUS  STELLA_CODE  STEPHEN_STATUS  STEPHEN_CODE  TRACY_STATUS  TRACY_CODE
  ------------        --------  ------------   ----------  ------------  ----------  -------------  -----------  -----------  ---------   -------------  -----------  --------------  ------------  ------------  ----------
  
  
  2017-11-10 10:00:00     DB-3452     PRESENT       2344         PRESENT    2344        PRESENT            1001       PRESENT        1001          PRESENT     1001         PRESENT        1001      PRESENT          1001
  
  2017-11-10 14:00:00     DB-3452     PRESENT       1001         PRESENT    1001        PRESENT            1001       PRESENT        1001          PRESENT     1001         PRESENT        1001      PRESENT          2344
  
  2017-11-11 11:24:00     DB-3452     PRESENT       1001         PRESENT    1001        PRESENT            1001       PRESENT        1001          PRESENT     1001         PRESENT        1001      PRESENT          1001
  
  2017-11-11 14:24:00     DB-3452     PRESENT       2344         PRESENT    2344        PRESENT            2344       PRESENT        2344          PRESENT     2344         PRESENT        1001      PRESENT          1001
  </code></pre>
  
  <p>From the above result , you can see the data is incorrect .</p>
  
  <p><strong>Lets say for James , he is absent for two sessions which can be seen from his code 2344 , but the Status is still showing as Present.The same issue happens with the whole set of records</strong>.</p>
  
  <p>What am i doing wrong ?</p>
  
  <p>Any suggestions &amp; inputs are greatly appreciated?</p>