sqloracle-databaseoracle11gdynamic-queries

flattening oracle table rows to a single row with multiple columns


I have an audit table maintaining the user status as an when it changes. The table is something like below.

USER_ENTL_ID    USER_STATUS CREATED_Date
1                  S         10/20/2017
1                  C         10/21/2017
1                  W         10/22/2017
1                  SP        10/23/2017
2                  S         10/24/2017
2                  C         10/25/2017

Every user might not transition to each and every state in the application and that is Ok. what i want to do is capture the date and the status for each user when it changed and have it in a single row.

So the output I am expecting it to be something like below.

id  S_status    s_created   c_status    c_created   W_status    w_created   sp_STATUS   SP_CREATED
1   S           10/20/2017    C         10/21/2017   W          10/22/2017      SP      10/23/2017
2   S           10/24/2017    C         10/25/2017              

I have been reading about pivot and unpivot and decode but I am not sure if this is even doable in oracle. and if yes can some one guide me on the right path ?


Solution

  • Use of "conditional aggregates" is a traditional, and still effective, way of handling such needs:

    SQL Fiddle

    PostgreSQL 9.6 Schema Setup:

    CREATE TABLE AUDIT_TABLE
        (USER_ENTL_ID int, USER_STATUS varchar(2), CREATED_DATE timestamp)
    ;
    
    INSERT INTO AUDIT_TABLE
        (USER_ENTL_ID, USER_STATUS, CREATED_DATE)
    VALUES
        (1, 'S', '2017-10-20 00:00:00'),
        (1, 'C', '2017-10-21 00:00:00'),
        (1, 'W', '2017-10-22 00:00:00'),
        (1, 'SP', '2017-10-23 00:00:00'),
        (2, 'S', '2017-10-24 00:00:00'),
        (2, 'C', '2017-10-25 00:00:00')
    ;
    

    Query 1:

    nb, the use of either MIN or MAX in this may matter depending on your data, but i there is only one value in the data per output location then either function can be used.

    SELECT
          USER_ENTL_ID
        , MAX(CASE WHEN USER_STATUS = 'S' THEN USER_STATUS END) s_status
        , MIN(CASE WHEN USER_STATUS = 'S' THEN CREATED_DATE END) s_created
        , MAX(CASE WHEN USER_STATUS = 'C' THEN USER_STATUS END) c_status
        , MIN(CASE WHEN USER_STATUS = 'C' THEN CREATED_DATE END) c_created
        , MAX(CASE WHEN USER_STATUS = 'W' THEN USER_STATUS END) w_status
        , MIN(CASE WHEN USER_STATUS = 'W' THEN CREATED_DATE END) w_created
        , MAX(CASE WHEN USER_STATUS = 'SP' THEN USER_STATUS END) sp_status
        , MIN(CASE WHEN USER_STATUS = 'SP' THEN CREATED_DATE END) sp_created
    FROM AUDIT_TABLE
    GROUP BY 
          USER_ENTL_ID
    

    Results:

    | user_entl_id | s_status |            s_created | c_status |            c_created | w_status |            w_created | sp_status |           sp_created |
    |--------------|----------|----------------------|----------|----------------------|----------|----------------------|-----------|----------------------|
    |            1 |        S | 2017-10-20T00:00:00Z |        C | 2017-10-21T00:00:00Z |        W | 2017-10-22T00:00:00Z |        SP | 2017-10-23T00:00:00Z |
    |            2 |        S | 2017-10-24T00:00:00Z |        C | 2017-10-25T00:00:00Z |   (null) |               (null) |    (null) |               (null) |
    

    ADDED

    To explain further: If you remove the MIN or MAX functions and also remove the group by, this is what you get:

    +--------------+----------+----------------------+----------+----------------------+----------+----------------------+-----------+----------------------+
    | user_entl_id | s_status |      s_created       | c_status |      c_created       | w_status |      w_created       | sp_status |      sp_created      |
    +--------------+----------+----------------------+----------+----------------------+----------+----------------------+-----------+----------------------+
    |            1 | S        | 2017-10-20T00:00:00Z | (null)   | (null)               | (null)   | (null)               | (null)    | (null)               |
    |            1 | (null)   | (null)               | C        | 2017-10-21T00:00:00Z | (null)   | (null)               | (null)    | (null)               |
    |            1 | (null)   | (null)               | (null)   | (null)               | W        | 2017-10-22T00:00:00Z | (null)    | (null)               |
    |            1 | (null)   | (null)               | (null)   | (null)               | (null)   | (null)               | SP        | 2017-10-23T00:00:00Z |
    |            2 | S        | 2017-10-24T00:00:00Z | (null)   | (null)               | (null)   | (null)               | (null)    | (null)               |
    |            2 | (null)   | (null)               | C        | 2017-10-25T00:00:00Z | (null)   | (null)               | (null)    | (null)               |
    +--------------+----------+----------------------+----------+----------------------+----------+----------------------+-----------+----------------------+
    

    If you study that you will see that for the data we care about there is only one value per row (for each USER_ENTL_ID) but they are spread over several rows. So the MIN/MAX functions and the GROUP BY "flatten" the result so we end-up with the wanted result. QED