How can I convert rows to columns in Oracle without subqueries and display all possible values for a column even if some do not exist?

Question

We are using Oracle 19c. I'm trying to write a query that will convert values into a columns. Here's sample data:

TestID	UserID	TestType	Result	TestDate
0001	0001	TestA01	+	03-AUG-17
0001	0001	TestA01	+	11-MAR-23
0002	0001	TestA02	-	11-MAR-23
0003	0001	TestB08	-	11-MAR-23
0004	0001	TestB17	+	11-MAR-23
0005	0002	TestA01	+	11-MAR-23
0006	0002	TestB08	+	11-MAR-23
0007	0003	TestA02	-	11-MAR-23

Here's how I'd like to display the data:

AnimalID	A01	A02	B08	B17
0001	+	-	-	+
0002	+	U	U	+
0003	U	-	U	U

My constraints - only take the latest test date (entirely possible that there are multiple results for each User); list all 4 test types and if no value, list 'U' or blank.

I know how to do ListAgg over values, but I don't know how to force display of all 4 values even when some don't exist. I've done it using four subqueries (one for each A01, A02 B08, B17) but the query performance is then far too slow. I welcome your suggestions.

Do you know your values before hand? And if you do where is that list of known values? (Is it in another table?) — Hogan, May 31 '23 at 16:27
Multi steps. 1) use a CTE to get a distinct list of the right 3 characters from testtype. 2) get a distinct list of animalIDs CTE. 3) cross join results from those two. This ensures every user/amimal has all possible test types. 4) take the resutls from that and left join to your original table on the testtype and userId. and coalesce the results so a coalesce(result,'U') THen use dynamic SQL to pivot the results based on the columns from your 1st cte. Dynamic: https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracles-sql — xQbert, May 31 '23 at 16:38
Or ask tom pivot: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238 As for getting the latest record for each test, use a row_number() over (partition by userID and TestType, TestID order by testDate Desc) and only look at rn =1 . Rule of thumb, break the problem down into steps and then solve each step. Once you have something that works; look for ways to make it work better. Do something, laugh, do it right, make it better. — xQbert, May 31 '23 at 16:45
Do you know all the values of TestType? All the values of TestType, do you know them? If not then you need dynamic SQL if you do know all the values please let us know what they are. — Hogan, May 31 '23 at 17:09
You could try https://technology.amis.nl/oracle/dynamic-sql-pivoting-stealing-antons-thunder/, and with this query select * from pivot(q'{select UserID, TestType, result from ( select UserID, TestType, result, row_number() over(partition by TestID, userid order by TestDate desc) as rn from the_data_table ) where rn = 1}') ; you will get almost the expected result but to get 'U' instead of NULL, you will have to dive in the code and adapt it. — p3consulting, May 31 '23 at 18:57

d r · Answer 1 · 2023-05-31T21:13:37.317

If you know the values that could appear in TEST_TYPE column - you could use PIVOT statement:

WITH      -- Sample Data
    tbl AS 
        (   Select  '0001' "TEST_ID",   '0001' "USER_ID",   'TestA01' "TEST_TYPE",  '+' "A_RESULT", To_Date('03-AUG-17', 'dd-MON-yy') "TEST_DATE" From Dual Union All
            Select  '0001', '0001', 'TestA01',  '+', To_Date('11-MAR-23', 'dd-MON-yy') From Dual Union All
            Select  '0002', '0001', 'TestA02',  '-', To_Date('11-MAR-23', 'dd-MON-yy') From Dual Union All
            Select  '0003', '0001', 'TestB08',  '-', To_Date('11-MAR-23', 'dd-MON-yy') From Dual Union All
            Select  '0004', '0001', 'TestB17',  '+', To_Date('11-MAR-23', 'dd-MON-yy') From Dual Union All
            Select  '0005', '0002', 'TestA01',  '+', To_Date('11-MAR-23', 'dd-MON-yy') From Dual Union All
            Select  '0006', '0002', 'TestB08',  '+', To_Date('11-MAR-23', 'dd-MON-yy') From Dual Union All
            Select  '0007', '0003', 'TestA02',  '-', To_Date('11-MAR-23', 'dd-MON-yy') From Dual 
        )
--  M a i n   S Q L :
Select USER_ID "ANIMAL_ID", Nvl(Max(A01), 'U') "A01", Nvl(Max(A02), 'U') "A02", Nvl(Max(B08), 'U') "B08", Nvl(Max(B17), 'U') "B17"
From (  Select * 
        From tbl
     )  PIVOT( Max(A_RESULT) FOR TEST_TYPE IN('TestA01' as A01, 'TestA02' as A02, 'TestB08' as B08, 'TestB17' as B17)  )
Group By USER_ID
Order By USER_ID
--  
--  R e s u l t :
--  AnimalID  A01  A02  B08  B17
--  --------  ---  ---  ---  ---
--  0001      +    -    -    +
--  0002      +    U    U    +
--  0003      U    -    U    U

And if you want just latest test date then resulting dataset is a bit different:

Select USER_ID "ANIMAL_ID", Nvl(Max(A01), 'U') "A01", Nvl(Max(A02), 'U') "A02", Nvl(Max(B08), 'U') "B08", Nvl(Max(B17), 'U') "B17"
From (  Select * 
        From tbl
        Where TEST_DATE = (Select Max(TEST_DATE) From tbl)
     )  PIVOT( Max(A_RESULT) FOR TEST_TYPE IN('TestA01' as A01, 'TestA02' as A02, 'TestB08' as B08, 'TestB17' as B17)  )
Group By USER_ID
Order By USER_ID
--  
--  R e s u l t :
--  AnimalID  A01  A02  B08  B17
--  --------  ---  ---  ---  ---
--  0001      +    -    -    +
--  0002      +    U    +    U
--  0003      U    -    U    U

How can I convert rows to columns in Oracle without subqueries and display all possible values for a column even if some do not exist?

1 Answers1