I have data that is denormalized and I want to grab the the column for each person where there is a value in the max column. If there is a value for Jump2 there will be a value for Jump1; there will not be a null value for the prior column.

Sample data:

I have data that is denormalized and I want to grab the the column for each person where there is a value in the max column. If there is a value for Jump2 there will be a value for Jump1; there will not be a null value for the prior column.

Sample data:

Expected result:

What query creates a temporary table?

I was thinking of creating a pivot table.

• sql
• oracle-database
• pivot-table
• denormalized

4 Answers 4

Based on your description, I would suggest the GREATEST() function

select name, greatest(nvl(jump1,0), nvl(jump2,0), nvl(jump3,0)) jump
from your_table

But this may not scale to your exact needs.

A quite simple idea would be to combine GREATEST (to fetch the maximum value of the columns) and COALESCE (to replace NULL by another value):

SELECT
  name,
  GREATEST(COALESCE(jump1,0), COALESCE(jump2,0), COALESCE(jump3,0)) AS Jump
FROM yourtable;

This idea has two disadvantages:

1. If you need a lot of columns, it's very unpleasant to list them all with COALESCE.
2. You need to make sure that the value in COALESCE (0 in my example) will never be the highest value of the columns. Depending on the possible values in the columns, this could make your query unreadable and/or unsafe.

Therefore I would use following UNPIVOT INCLUDE NULLS trick which avoids both possible issues of the previous query:

SELECT 
  name, 
  MAX(jump) AS jump
FROM yourtable
  UNPIVOT INCLUDE NULLS
  (jump FOR colname IN (jump1, jump2, jump3))
GROUP BY name;

See this db<>fiddle with your sample data.

Look at an example where column names are not used and their number is not limited.

select b.*,maxJump 
from(
  select t.*
    ,json_query(json_transform(json_object(t.*),REMOVE '$.NAME'),'$.*' with wrapper)njt
  from test t
)b
cross join lateral(
    select cast(max(value) as number(38))maxJump 
    from json_table(njt,'$[*]' COLUMNS (value PATH '$'))
    where value<>'null'
  )mjv

With test data as

Result is

fiddle

If you want one of the shortest to type:

select name, (select max(column_value) from sys.odcinumberlist(jump1, jump2, jump3)) as maxjump 
from data;