By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE table_name (field_x, field_y, watermelon, orange, cabbage) AS
SELECT 'lorem', 'ipsum', 4, 2, 5 FROM DUAL UNION ALL
SELECT 'dolor', 'sit', 9, 0, 7 FROM DUAL UNION ALL
SELECT 'amet', 'elit', 6, 9, 1 FROM DUAL;
3 rows affected
SELECT field_x,
field_y,
CASE
WHEN watermelon = GREATEST(watermelon, orange, cabbage)
THEN 'watermelon'
WHEN orange = GREATEST(watermelon, orange, cabbage)
THEN 'orange'
WHEN cabbage = GREATEST(watermelon, orange, cabbage)
THEN 'cabbage'
END AS fruit
FROM table_name
| FIELD_X | FIELD_Y | FRUIT |
|---|---|---|
| lorem | ipsum | cabbage |
| dolor | sit | watermelon |
| amet | elit | orange |
SELECT field_x,
field_y,
SUBSTR(
CASE
WHEN watermelon = GREATEST(watermelon, orange, cabbage)
THEN ', watermelon'
END
||
CASE
WHEN orange = GREATEST(watermelon, orange, cabbage)
THEN ', orange'
END
||
CASE
WHEN cabbage = GREATEST(watermelon, orange, cabbage)
THEN ', cabbage'
END,
3
) AS fruit
FROM table_name
| FIELD_X | FIELD_Y | FRUIT |
|---|---|---|
| lorem | ipsum | cabbage |
| dolor | sit | watermelon |
| amet | elit | orange |
SELECT field_x,
field_y,
MAX(fruit) KEEP (DENSE_RANK LAST ORDER BY value) AS fruit
FROM table_name
UNPIVOT (
value
FOR fruit IN (
watermelon AS 'watermelon',
orange AS 'orange',
cabbage AS 'cabbage'
)
)
GROUP BY field_x, field_y
| FIELD_X | FIELD_Y | FRUIT |
|---|---|---|
| amet | elit | orange |
| dolor | sit | watermelon |
| lorem | ipsum | cabbage |