Help with using an "IFF" with "OR"

[Heath Bohannan]

Hi group,

I am new to this world, and hope you could help with with the following. I am trying to return a single row where **status = Y and ** status = N
i.e. a client is in both Seg & NonSeg (underlying clients are split within legal entity). Hence my attempt with the row starting --

currently, if they have both then the output is on two separate rows

select eg.legalEntityId,le.name

,iif(eg.segregationStatus='S' ,'Y','') Seg

--,iif((eg.segregationStatus='S' and eg.segregationStatus='N','Y') or iff(eg.segregationStatus='S', 'Y') ,'') Seg

,iif(eg.segregationStatus='N','Y','') Non_Seg

from Account.Account a
join EquityGroup.EquityGroup eg on a.equityGroupId=eg.equityGroupId
join LegalEntity.LegalEntity le on le.legalEntityId=eg.legalEntityId
where eg.positionHolderTypeId<>'b'and a.archived='n'and segregationStatus <>'h'
group by eg.legalEntityId,segregationStatus, le.name


Any help would be appreciated

Regards