I have a table

USER_ID    TYPE     VALUE    AUDIT_USER   AUDIT_DATE
1          FIELD1   234565   TEST_USER1   1/21/2022
1          FIELD2   TEST1    TEST_USER2   2/11/2022
2          FIELD2   TEST34   TEST_USER1   3/1/2022
2          FIELD3   678ABC   TEST_USER1   2/22/2022

I want to show data pivoted as:

USER_ID  FIELD1    FIELD2    FIELD3
1        234565    TEST1 
2                  TEST34    678ABC

I was able to accomplish that using PIVOT query:

SELECT USER_ID, FIELD1, FIELD2, FIELD3
        FROM 
        (SELECT USER_ID, TYPE, VALUE FROM TABLE1
        ) PIVOT
        (
            MAX(value)
            FOR type IN ('FIELD1' as FIELD1, 'FIELD2' as FIELD2, 'FIELD3' as FIELD3)
        )

Now I need to add a latest updated date and latest audit user into the mix so my data would look like this:

USER_ID  FIELD1    FIELD2    FIELD3   LATEST_AUDIT_DATE  LATEST_AUDIT_USER
1        234565    TEST1              2/11/2022          TEST_USER2
2                  TEST34    678ABC   3/1/2022           TEST_USER1

So I changed my query to

SELECT USER_ID, FIELD1, FIELD2, FIELD3, LAST_AUDIT_USER, LAST_AUDIT_DATE
        FROM 
        (SELECT t1.USER_ID, 
                t1.TYPE, 
                 t1.VALUE,                     
                 (SELECT MAX(AUDIT_DATE) FROM TABLE1 WHERE USER_ID=t1.USER_ID)  LAST_AUDIT_DATE,
                 (SELECT MAX(AUDIT_USER) FROM TABLE1 WHERE USER_ID=t1.USER_ID AND AUDIT_DATE=((SELECT MAX(AUDIT_DATE) FROM TABLE1 WHERE USER_ID=t1.USER_ID)) LAST_AUDIT_USER
                  FROM TABLE1 t1 GROUP BY USER_ID, TYPE, VALUE
        ) PIVOT
        (
            MAX(value)
            FOR type IN ('FIELD1' as FIELD1, 'FIELD2' as FIELD2, 'FIELD3' as FIELD3)
        )

AND that gives me the desired result

USER_ID  FIELD1    FIELD2    FIELD3   LATEST_AUDIT_DATE  LATEST_AUDIT_USER
1        234565    TEST1              2/11/2022          TEST_USER2
2                  TEST34    678ABC   3/1/2022           TEST_USER1

but I was wondering if there is a better way to accomplish the same thing as my query seems overly complicated

CodePudding user response：

You can filter the rows to get only the latest row for each user_id, type group and then use conditional aggregation:

SELECT user_id,
       MAX(CASE type WHEN 'FIELD1' THEN value END) AS field1,
       MAX(CASE type WHEN 'FIELD2' THEN value END) AS field2,
       MAX(CASE type WHEN 'FIELD3' THEN value END) AS field3,
       MAX(audit_date) AS latest_audit_date,
       MAX(audit_user) KEEP (DENSE_RANK LAST ORDER BY audit_date)
         AS latest_audit_user
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY user_id, type ORDER BY audit_date DESC) AS rn
  FROM   table1 t
  WHERE  type IN ('FIELD1', 'FIELD2', 'FIELD3')
)
WHERE  rn = 1
GROUP BY user_id

Which, for the sample data:

CREATE TABLE table1 (USER_ID, TYPE, VALUE, AUDIT_USER, AUDIT_DATE) AS
SELECT 1, 'FIELD1', '234565', 'TEST_USER1', DATE '2022-01-21' FROM DUAL UNION ALL
SELECT 1, 'FIELD2', 'TEST1',  'TEST_USER2', DATE '2022-02-11' FROM DUAL UNION ALL
SELECT 2, 'FIELD2', 'TEST34', 'TEST_USER1', DATE '2022-03-01' FROM DUAL UNION ALL
SELECT 2, 'FIELD3', '678ABC', 'TEST_USER1', DATE '2022-02-22' FROM DUAL;

Outputs:

USER_ID	FIELD1	FIELD2	FIELD3	LATEST_AUDIT_DATE	LATEST_AUDIT_USER
1	234565	TEST1	null	2022-02-11 00:00:00	TEST_USER2
2	null	TEST34	678ABC	2022-03-01 00:00:00	TEST_USER1

db<>fiddle here

CodePudding user response：

You can try to use ROW_NUMBER window function in subquery then do condition aggregate function

Query 1:

SELECT USER_ID,
       MAX(CASE WHEN TYPE = 'FIELD1' THEN VALUE END)  FIELD1 , 
       MAX(CASE WHEN TYPE = 'FIELD2' THEN VALUE END)   FIELD2, 
       MAX(CASE WHEN TYPE = 'FIELD3' THEN VALUE END)   FIELD3,
       MAX(CASE WHEN rn = 1 THEN AUDIT_DATE END) LATEST_AUDIT_DATE,
       MAX(CASE WHEN rn = 1 THEN AUDIT_USER END) LATEST_AUDIT_USER
FROM (
    SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY AUDIT_DATE DESC) rn
    FROM TABLE1  t1
) t1
GROUP BY USER_ID

Results:

| USER_ID | FIELD1 | FIELD2 | FIELD3 | LATEST_AUDIT_DATE | LATEST_AUDIT_USER |
|---------|--------|--------|--------|-------------------|-------------------|
|       1 | 234565 |  TEST1 | (null) |         2/11/2022 |        TEST_USER2 |
|       2 | (null) | TEST34 | 678ABC |          3/1/2022 |        TEST_USER1 |