flattening oracle table rows to a single row with multiple columns

Question

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 ?

Answer 1

Just use PIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE AUDIT_TABLE (USER_ENTL_ID, USER_STATUS, CREATED_DATE) AS
SELECT 1, 'S',  TIMESTAMP '2017-10-20 00:00:00' FROM DUAL UNION ALL
SELECT 1, 'C',  TIMESTAMP '2017-10-21 00:00:00' FROM DUAL UNION ALL
SELECT 1, 'W',  TIMESTAMP '2017-10-22 00:00:00' FROM DUAL UNION ALL
SELECT 1, 'SP', TIMESTAMP '2017-10-23 00:00:00' FROM DUAL UNION ALL
SELECT 2, 'S',  TIMESTAMP '2017-10-24 00:00:00' FROM DUAL UNION ALL
SELECT 2, 'C',  TIMESTAMP '2017-10-25 00:00:00' FROM DUAL

Query 1:

SELECT *
FROM   AUDIT_TABLE
PIVOT (
  MAX( Created_Date ) AS Created,
  MAX( User_Status ) AS  Status
  FOR User_Status IN (
    'S' AS S, 'C' AS C, 'W' AS W, 'SP' AS SP
  )
)

Results:

| USER_ENTL_ID |             S_CREATED | S_STATUS |             C_CREATED | C_STATUS |             W_CREATED | W_STATUS |            SP_CREATED | SP_STATUS |
|--------------|-----------------------|----------|-----------------------|----------|-----------------------|----------|-----------------------|-----------|
|            1 | 2017-10-20 00:00:00.0 |        S | 2017-10-21 00:00:00.0 |        C | 2017-10-22 00:00:00.0 |        W | 2017-10-23 00:00:00.0 |        SP |
|            2 | 2017-10-24 00:00:00.0 |        S | 2017-10-25 00:00:00.0 |        C |                (null) |   (null) |                (null) |    (null) |

Answer 2

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

Answer 3

Something like the following may be easier to understand:

WITH cteID AS (SELECT DISTINCT USER_ENTL_ID AS ID FROM AUDIT_TABLE),
     cteS  AS (SELECT USER_ENTL_ID AS ID,
                      'S' AS S_STATUS,
                      MIN(CREATED_DATE) AS S_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'S'
                 GROUP BY USER_ENTL_ID),
     cteC AS  (SELECT USER_ENTL_ID AS ID,
                      'C' AS C_STATUS,
                      MIN(CREATED_DATE) AS C_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'C'
                 GROUP BY USER_ENTL_ID),
     cteSP AS (SELECT USER_ENTL_ID AS ID,
                      'SP' AS SP_STATUS,
                      MIN(CREATED_DATE) AS SP_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'SP'
                 GROUP BY USER_ENTL_ID),
     cteW AS  (SELECT USER_ENTL_ID AS ID,
                      'W' AS W_STATUS,
                      MIN(CREATED_DATE) AS W_CREATED
                 FROM AUDIT_TABLE
                 WHERE STATUS = 'W'
                 GROUP BY USER_ENTL_ID)
SELECT i.ID,
       s.S_STATUS,
       s.S_CREATED,
       c.C_STATUS,
       c.C_CREATED,
       sp.SP_STATUS,
       sp.SP_CREATED,
       w.W_STATUS,
       w.W_CREATED
  FROM cteID i
  LEFT OUTER JOIN cteS s
    ON s.ID = i.ID
  LEFT OUTER JOIN cteC c
    ON c.ID = i.ID
  LEFT OUTER JOIN cteSP sp
    ON sp.ID = i.ID
  LEFT OUTER JOIN cteW w
    ON w.ID = i.ID

Best of luck.