How to eliminate null and display value instead

Question

I have the table as below

| EMPID    | MESASURE |          | MEASURE_VAL1    |MEASURE_VAL2    |MEASURE_VAL3   |
| -------- | --------------      |----------       |--------------  |--------------
| A        | MEASURE1            | 10              | NULL           | NULL
| A        | MEASURE2            | NULL            | 20             | NULL
| A        | MEASURE3            | NULL            | NULL           | 30
| A        | MEASURE4            | NULL            | NULL           | NULL

I want to get the result as

| EMPID | MEASURE 1 | MEASURE 2 | MEASURE 3
-------------------------------------------
| A     |  10       | 20        | 30

I tried with where not null and get only one value. Please suggest. Thanks

Do you want a MEASURE 4 column to suddenly show up if someone later adds a MMEASURE4 value? (And what about MEASURE5, 6 and 7?) — jarlh, Dec 28 '21 at 10:10
Use MAX aggregation for each measure_val along with grouping by empid — Barbaros Özhan, Dec 28 '21 at 10:14
https://www.oracletutorial.com/oracle-basics/oracle-pivot/ might help. — jarlh, Dec 28 '21 at 10:18
What happens if more than 1 measure value is not null for a row? — Stu, Dec 28 '21 at 10:22

MT0 · Answer 1 · 2021-12-28T10:47:08.233

Use the SUM (or MAX) aggregation function:

SELECT empid,
       SUM(measure_val1) AS measure1,
       SUM(measure_val2) AS measure2,
       SUM(measure_val3) AS measure3
FROM   table_name
GROUP BY empid;

Which, for the sample data:

CREATE TABLE table_name (EMPID, MESASURE, MEASURE_VAL1, MEASURE_VAL2, MEASURE_VAL3) AS
SELECT 'A', 'MEASURE1', 10,   NULL, NULL FROM DUAL UNION ALL
SELECT 'A', 'MEASURE2', NULL, 20,   NULL FROM DUAL UNION ALL
SELECT 'A', 'MEASURE3', NULL, NULL, 30   FROM DUAL UNION ALL
SELECT 'A', 'MEASURE4', NULL, NULL, NULL FROM DUAL;

Outputs:

EMPID MEASURE1 MEASURE2 MEASURE3

A 10 20 30

db<>fiddle here

score 0 · Accepted Answer · answered Dec 28 '21 at 10:41

To me, it looks like

SQL> with test (empid, measure, measure_val1, measure_val2, measure_val3) as
  2    (select 'A', 'MEASURE1', 10  , null, null from dual union all
  3     select 'A', 'MEASURE2', null, 20  , null from dual union all
  4     select 'A', 'MEASURE3', null, null, 30   from dual union all
  5     select 'A', 'MEASURE4', null, null, null from dual
  6    )
  7  select empid,
  8    max(case when measure = 'MEASURE1' then nvl(measure_val1, 0) + nvl(measure_val2, 0) + nvl(measure_val3, 0) end) measure_1,
  9    max(case when measure = 'MEASURE2' then nvl(measure_val1, 0) + nvl(measure_val2, 0) + nvl(measure_val3, 0) end) measure_2,
 10    max(case when measure = 'MEASURE3' then nvl(measure_val1, 0) + nvl(measure_val2, 0) + nvl(measure_val3, 0) end) measure_3,
 11    max(case when measure = 'MEASURE4' then nvl(measure_val1, 0) + nvl(measure_val2, 0) + nvl(measure_val3, 0) end) measure_4
 12  from test
 13  group by empid;

EMPID  MEASURE_1  MEASURE_2  MEASURE_3  MEASURE_4
----- ---------- ---------- ---------- ----------
A             10         20         30          0

SQL>

Thank you, it works however, is there alternative way other than max function. — selvan, Dec 28 '21 at 10:54
You're welcome. Alternative? Sure, MIN function. Partially *kidding*, but - you do need an aggregate function if you want to have result in a single row. — Littlefoot, Dec 28 '21 at 11:20

How to eliminate null and display value instead

2 Answers2