How to eliminate null and display value instead-CodePudding

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

CodePudding user response：

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

EMPID	MEASURE1	MEASURE2	MEASURE3
A	10	20	30

db<>fiddle here

CodePudding user response：

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>