CASE statement returns NULLS for rows in the other columns

Question

CASE statement is returning null for the empty rows of the other column.

Here is my code:

SELECT
    EMPLOYEE_ID,
    CASE WHEN Category = 'Ethnic_Group' THEN EMPLOYEE_ETHNICITY
        ELSE NULL
    END AS 'ethnicity',
    CASE WHEN Category = 'RACE' THEN EMPLOYEE_RACE
        ELSE NULL
    END AS 'race'
FROM EMPLMOYEE_TABLE

employee_id	ethnicity	race
1	African-American	NULL
1	NULL	OTHERS
1	NULL	BLACK

I would like something similar to this:

employee_id	ethnicity	race
1	African-American	BLACK
1	African-American	OTHERS

Answer 1

Do you mind sharing what is happening along with what you want to have happen? I see there are NULL fields in the ethnicity column. If you can walk the ethnicity column from the race column (and there aren't nulls here as well) I'd do:

SELECT
    EMPLOYEE_ID,
    CASE 
        WHEN EMPLOYEE_RACE = 'BLACK' 
            THEN COALESCE(EMPLOYEE_ETHNICITY, 'African-American')
        ELSE NULL
    END AS 'ethnicity',
    CASE 
        WHEN EMPLOYEE_ETHNICITY = 'African-American' 
            THEN COALESCE(EMPLOYEE_RACE, 'BLACK')
        ELSE NULL
    END AS 'race'
FROM 
    EMPLOYEE_TABLE

Answer 2

It's about filling NULL holes with the data from previous rows.

I'm taking advantage that the sort I use, by ethnicity follows the NULLS LAST rule.

WITH
-- your input, don't use in final query ...
employee_table(employee_id,ethnicity,race) AS (
          SELECT 1,'African-American',NULL
UNION ALL SELECT 1,  NULL            ,'OTHERS'
UNION ALL SELECT 1,  NULL            ,'BLACK'
)
-- real query starts here, replace following comma with "WITH" ...
,
holes_filled AS (
  SELECT
    employee_id
  , LAST_VALUE(ethnicity IGNORE NULLS) OVER w
  , race
  FROM employee_table
  WINDOW w AS (PARTITION BY employee_id ORDER BY ethnicity)
)
SELECT 
  * 
FROM holes_filled
WHERE race IS NOT NULL;
-- out  employee_id |    LAST_VALUE    |  race  
-- out -------------+------------------+--------
-- out            1 | African-American | OTHERS
-- out            1 | African-American | BLACK