CODEWITHSUNDEEP
oracle-databaseoracle11gpivotoracle-sqldeveloperoracle12c
jaggs
jaggs

Reputation: 308

PIVOT function with String columns return wrong output result

I have two tables named

class_record_hist which will store the attendance entry of a class for a particular date

student_record_hist which will store the list of students attendance for the particular class.Here the transaction_id will be the primary key for both tables

MY Data looks like this 

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

I will be generating a report based on this records

And this is my query like 

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 >= to_date('2017-11-10 06:00:00','yyyy-mm-dd hh24:mi:ss') 
and a.date_tm  <= 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');
) ;

Result:

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

From the above result , you can see the data is incorrect .

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.

What am i doing wrong ?

Any suggestions & inputs are greatly appreciated?

Upvotes: 1

Views: 235

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35623

The min(student_status) equates to the max(attendance_code)

  • Absent == 2344
  • Present == 1001

so adjust the pivot accordingly

select * 
from ( 
  select 
          student_name, student_status,attendance_code,working_date,class_id 
  from ( 
        your_current_query
        ) d
  ) d2
  PIVOT ( 
        max(attendance_code) as code, 
        min(student_status) as status
        for student_name in ('JAMES','PETER','MURPHY','MICA','STELLA','STEPHEN','TRACY')
         )
;

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE MY_RESULT
    (WORKING_DATE timestamp, STUDENT_NAME varchar2(7), STUDENT_STATUS varchar2(7), ATTENDANCE_CODE int, TEACHER_UPDATE varchar2(2), TRANSACTION_ID int)
;

INSERT ALL 
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'JAMES', 'ABSENT', 2344, 'AB', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'PETER', 'ABSENT', 2344, 'AB', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'MURPHY', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'MICA', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'STELLA', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'TRACY', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'JAMES', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'PETER', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'MURPHY', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'MICA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'STELLA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'TRACY', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'JAMES', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'PETER', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'MURPHY', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'MICA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'STELLA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'TRACY', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'JAMES', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'PETER', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'MURPHY', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'MICA', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'STELLA', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'TRACY', 'PRESENT', 1001, 'PR', 23454)
SELECT * FROM dual
;

Query 1:

select * 
from ( 
  select 
          student_name, student_status,attendance_code,working_date,class_id 
  from ( 
        select * from my_result
        ) d
  ) d2
  PIVOT ( 
        max(attendance_code) as code, 
        min(student_status) as status
        for student_name in ('JAMES','PETER','MURPHY','MICA','STELLA','STEPHEN','TRACY')
         )

Results:

|          WORKING_DATE | 'JAMES'_CODE | 'JAMES'_STATUS | 'PETER'_CODE | 'PETER'_STATUS | 'MURPHY'_CODE | 'MURPHY'_STATUS | 'MICA'_CODE | 'MICA'_STATUS | 'STELLA'_CODE | 'STELLA'_STATUS | 'STEPHEN'_CODE | 'STEPHEN'_STATUS | 'TRACY'_CODE | 'TRACY'_STATUS |
|-----------------------|--------------|----------------|--------------|----------------|---------------|-----------------|-------------|---------------|---------------|-----------------|----------------|------------------|--------------|----------------|
| 2017-11-11 14:24:00.0 |         2344 |         ABSENT |         2344 |         ABSENT |          2344 |          ABSENT |        2344 |        ABSENT |          2344 |          ABSENT |           1001 |          PRESENT |         1001 |        PRESENT |
| 2017-11-11 11:24:00.0 |         1001 |        PRESENT |         1001 |        PRESENT |          1001 |         PRESENT |        1001 |       PRESENT |          1001 |         PRESENT |           1001 |          PRESENT |         1001 |        PRESENT |
| 2017-11-10 10:00:00.0 |         2344 |         ABSENT |         2344 |         ABSENT |          1001 |         PRESENT |        1001 |       PRESENT |          1001 |         PRESENT |           1001 |          PRESENT |         1001 |        PRESENT |
| 2017-11-10 14:00:00.0 |         1001 |        PRESENT |         1001 |        PRESENT |          1001 |         PRESENT |        1001 |       PRESENT |          1001 |         PRESENT |           1001 |          PRESENT |         2344 |         ABSENT |

Over 25 years ago ANSI standards formalized "explicit" join syntax, it's about time you got on board with this change. e.g.

        SELECT
              to_char(c.date_tm, 'yyyy-mm-dd hh24:mi:ss') AS working_date
            , s.student_name
            , s.student_status
            , s.attendance_code
            , s.teacher_update
            , c.transaction_id
        FROM class_record_hist c
        INNER JOIN student_record_hist s ON c.transaction_id = s.transaction_id
        WHERE c.school = 'DON BOSCO'
        AND c.building = 'A1'
        AND c.class_id = 'DB-3452'
        AND c.date_tm >= to_date('2017-11-10 06:00:00', 'yyyy-mm-dd hh24:mi:ss')
        AND c.date_tm <= to_date('2017-11-11 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
        --ORDER BY
            --  working_date DESC
            --, s.student_name ASC

You shouldn't need an order by in that subquery by the way

Upvotes: 2

Related Questions