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:

name	jump1	jump2	jump3
John	10	15	13
Mary	5	17	null
David	8	null	null

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:

name	jump1	jump2	jump3
John	10	15	13
Mary	5	17	null
David	8	null	null

Expected result:

name	jump
John	15
Mary	17
David	8

What query creates a temporary table?

I was thinking of creating a pivot table.

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

Share Improve this question Follow edited Mar 4 at 7:51 Jonas Metzler 6,16833 gold badges99 silver badges2222 bronze badges asked Mar 4 at 1:22 Khanh HuaKhanh Hua 11 0

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 1

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

NAME	JUMP1	JUMP2	JUMP3
John	10	15	13
Mary	5	17	null
David	8	null	null

Result is

NAME	JUMP1	JUMP2	JUMP3	NJT	MAXJUMP
John	10	15	13	[10,"15","13"]	15
Mary	5	17	null	[5,"17","null"]	5
David	8	null	null	[8,"null","null"]	8

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;