Using Oracle combine three tables to one with PIVOT

I have three Oracle SQL select queries which returns following results. First select query returns result:

 user_id | user_name |
---------|-----------|
     1   |   user_1  |
     2   |   user_2  |
     3   |   user_3  |
     4   |   user_4  |

second select query returns result:

 exam_id | exam_name |
---------|-----------|
    1    |   exam_1  |
    2    |   exam_2  |
    3    |   exam_3  |

and the third select query returns result:

 exam_id | user_id | exam_date |
---------|---------|-----------|
    1    |    1    |    2017   |
    1    |    2    |    2018   |
    1    |    3    |    2017   |
    2    |    3    |    2018   |

I would like to combine these queries to get result:

 user_id | user_name | exam_1 | exam_2 | exam_3 |
---------|-----------|--------|--------|--------|
     1   |   user_1  |  2017  |        |        |
     2   |   user_2  |  2018  |        |        |
     3   |   user_3  |  2017  |  2018  |        |
     4   |   user_4  |        |        |        |

I would be grateful for any help?

1 answer

  • answered 2018-05-16 05:04 shrek

    This should get you going -

    CREATE TABLE users
        (user_id varchar2(9), user_name varchar2(11))
    ;
    
    INSERT ALL 
        INTO users (user_id, user_name)
             VALUES ('1', 'user_1')
        INTO users (user_id, user_name)
             VALUES ('2', 'user_2')
        INTO users (user_id, user_name)
             VALUES ('3', 'user_3')
        INTO users (user_id, user_name)
             VALUES ('4', 'user_4')
    SELECT * FROM dual
    ;
    
    CREATE TABLE exam
        (exam_id varchar2(9), exam_name varchar2(11))
    ;
    
    INSERT ALL 
        INTO exam (exam_id, exam_name)
             VALUES ('1', 'exam_1')
        INTO exam (exam_id, exam_name)
             VALUES ('2', 'exam_2')
        INTO exam (exam_id, exam_name)
             VALUES ('3', 'exam_3')
    SELECT * FROM dual
    ;
    
    CREATE TABLE exam_user
        (exam_id varchar2(9), user_id varchar2(9), exam_date varchar2(11))
    ;
    
    INSERT ALL 
        INTO exam_user (exam_id, user_id, exam_date)
             VALUES ('1', '1', '2017')
        INTO exam_user (exam_id, user_id, exam_date)
             VALUES ('1', '2', '2018')
        INTO exam_user (exam_id, user_id, exam_date)
             VALUES ('1', '3', '2017')
        INTO exam_user (exam_id, user_id, exam_date)
             VALUES ('2', '3', '2018')
    SELECT * FROM dual
    ;
    

    Query -

    SELECT * FROM (
    SELECT U.USER_ID, U.USER_NAME, E.EXAM_NAME,EU.EXAM_DATE
    FROM USERS U, EXAM E, EXAM_USER EU
    WHERE U.USER_ID = EU.USER_ID(+)
    AND E.EXAM_ID(+) = EU.EXAM_ID
    ORDER BY U.USER_ID
      )
      PIVOT (MAX(EXAM_DATE) FOR EXAM_NAME IN ('exam_1' as exam_1, 'exam_2' as exam_2,'exam_3' as exam_3))
      order by 1
    ;
    

    Output -

    USER_ID USER_NAME   EXAM_1  EXAM_2  EXAM_3
    1   user_1  2017    (null)  (null)
    2   user_2  2018    (null)  (null)
    3   user_3  2017    2018    (null)
    4   user_4  (null)  (null)  (null)