How to group by two columns in different CASE clauses

I have a query that’s giving me the results I want, but for each item with a given ID_UNIDAD_EXPERIMENTAL there are two rows, one with column “Alt” with a value and column “Dap” with null, and the other one with “Alt” null and “Dap” with a value. My question is: How can I group them so each element with a given ID just shows both Alt and Dap columns with values, kind of “removing” the nulls.

Heres the query:

SELECT ENS_Medicion.id_medicion_resumen, mr.id_ensayo, ENS_Medicion.id_unidad_exprimental,
    CASE WHEN UPPER(Variable_simple.nombre) = UPPER('alt') THEN CONVERT(decimal(18,7), ISNULL(NULLIF(valor_medido, ''), '0')) END as alt,
    CASE WHEN UPPER(Variable_simple.nombre) = UPPER('dap') THEN CONVERT(decimal(18,7), ISNULL(NULLIF(valor_medido, ''), '0')) END as dap
FROM ENS_Medicion 
JOIN ENS_Variable_medicion ON ENS_Variable_medicion.id_variable_medicion = ENS_Medicion.id_variable_med 
JOIN Variable_simple ON Variable_simple.id_variable_simple = ENS_Variable_medicion.id_variable_simple
join ENS_Variable_medicion varmed on ENS_Medicion.id_variable_med = varmed.id_variable_medicion
join Variable_simple vs on varmed.id_variable_simple = vs.id_variable_simple 
join ENS_Medicion_resumen mr on mr.id_medicion_resumen = ENS_Medicion.id_medicion_resumen
where mr.nro_medicion = 3 and mr.id_ensayo = 9227 and (UPPER(Variable_simple.nombre) = UPPER('alt') or UPPER(Variable_simple.nombre) = UPPER('dap'))
GROUP BY ENS_Medicion.id_medicion_resumen, ENS_Medicion.id_unidad_exprimental, Variable_simple.nombre, valor_medido, mr.id_ensayo, ENS_Medicion.id_unidad_exprimental
ORDER BY id_unidad_exprimental

and here are the results:

you can see that there are two rows for each “id_unidad_experimental” and each row has one column with a null value, the goal is to only show one row for “id_unidad_experimental” with the two values.

Any help would be nice

Answer

Try it like:

WITH cte as(
SELECT ENS_Medicion.id_medicion_resumen, mr.id_ensayo, ENS_Medicion.id_unidad_exprimental,
        CASE WHEN UPPER(Variable_simple.nombre) = UPPER('alt') THEN CONVERT(decimal(18,7), ISNULL(NULLIF(valor_medido, ''), '0')) END as alt,
        CASE WHEN UPPER(Variable_simple.nombre) = UPPER('dap') THEN CONVERT(decimal(18,7), ISNULL(NULLIF(valor_medido, ''), '0')) END as dap
    FROM ENS_Medicion 
    JOIN ENS_Variable_medicion ON ENS_Variable_medicion.id_variable_medicion = ENS_Medicion.id_variable_med 
    JOIN Variable_simple ON Variable_simple.id_variable_simple = ENS_Variable_medicion.id_variable_simple
    join ENS_Variable_medicion varmed on ENS_Medicion.id_variable_med = varmed.id_variable_medicion
    join Variable_simple vs on varmed.id_variable_simple = vs.id_variable_simple 
    join ENS_Medicion_resumen mr on mr.id_medicion_resumen = ENS_Medicion.id_medicion_resumen
    where mr.nro_medicion = 3 and mr.id_ensayo = 9227 and (UPPER(Variable_simple.nombre) = UPPER('alt') or UPPER(Variable_simple.nombre) = UPPER('dap'))
    GROUP BY ENS_Medicion.id_medicion_resumen, ENS_Medicion.id_unidad_exprimental, Variable_simple.nombre, valor_medido, mr.id_ensayo, ENS_Medicion.id_unidad_exprimental
    ORDER BY id_unidad_exprimental)

select t1.id_medicion_resumen, t1.id_ensayo, t1.id_unidad_exprimental, t1.alt, t2.dap 
from cte t1
inner join cte t2 on t1.id_medicion_resumen = t2.id_medicion_resumen and t1.id_ensayo = t2.id_ensayo and t1.id_unidad_exprimental = t2.id_unidad_exprimental
where t1.alt is not null 
and t2.dap is not null