SQL QUERY MERGE TWO ROW DATA

Question

Suppose My Database is like this :

MemberName  MemberID   ResultsEligibilityID
Thuso         2              1
Thuso         2              1
Maubane       3              2
Maubane       3              1
CDeveloper    5              2
CDeveloper    5              2

Now is it possible to write a query to display (The Below output) based on this:

if both ResultsEligibilityID for a single Member is 1 then Eligibile, Otherwise Non-Eligible.

OUTPUT

MemberName  MemberID  ResultsEligibilityID  Results
Thuso        2              1             Eligible
Maubane      3              2             Non-Eligible
CDeveloper   5              2             Non-Eligible

Thanks in advance for the help.

Answer 1

Please try:

select 
    MemberName,
    MemberID, 
    MAX(ResultsEligibilityID) ResultsEligibilityID ,
    (case when sum(case when ResultsEligibilityID=1 then 1 else 0 end)= COUNT(*) 
    then 'Eligible' else 'Non-Eligible' end) Results
From 
    YourTable
group by MemberName,MemberID