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 ?
Use of "conditional aggregates" is a traditional, and still effective, way of handling such needs:
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
| 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) |
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