How to put a field of a horizontal select in sql that does not duplicate data SqlServer

0
select 
Paciente= HC.IdPaciente ,
Fecha_Adm = CONVERT(char(10), E.FechaAdmision, 101),
Activo = Case when  E.Estado = 'Activo' then 1 end,
Anulado = Case when E.Estado = 'Anulado' then 1 end,
Estado = Case when E.Estado = 'Anulado' then 'Solo Anulado' 
              when E.Estado = 'Activado' and E.Estado= 'Anulado'  THEN 'Activado'  else 'Activado' end
from dbo.VW_SA_EM_REP_ListadoAtencionEmergencia E
--LEFT JOIN PersonaMast         Paciente        ON  Cita.IdPaciente         =   Paciente.Persona
LEFT JOIN SS_GE_Paciente        HC              ON  E.CodigoHC = HC.CodigoHC    
where 
Year(FechaAdmision)=2018 
and Month(FechaAdmision)=08
and TipoAtencion= 'Emergencia'
and E.Sucursal = 'CEG'
group by 
HC.IdPaciente, 
CONVERT(char(10), E.FechaAdmision, 101), 
Case when E.Estado = 'Activo' then 1 end,
Case when E.Estado = 'Anulado' then 1 end,
Case when E.Estado = 'Anulado' then 'Solo Anulado' 
     when E.Estado = 'Activado' and E.Estado= 'Anulado'  THEN 'Activado'  else 'Activado' end
order by Paciente, Fecha_Adm

I have this query, from which the result gives me an example if a patient has id 28 and its FechaAdmsion equals 01/10 but has two states Activo and Anulado .

Based on this example I will give two data that one has Activated and the other Canceled but the question would be how to make this patient who has two data put only one.

See here what you want to get

    
asked by Christian Guerra Huertas 29.10.2018 в 20:32
source

0 answers