Your requirement is:
IF Status1=0 and Status2 =1 then 'Red'
IF status3=0 and status4=1 then 'Green',
IF Status5=1 then 'Blue'
Then, your output should be:
ID [Status1 status2] [status3 status4] [Status5]
1 [(0,1) 'Red'] [(1,0) 'NA'] [(1) 'Blue']
2 [(1,1) 'NA'] [(0,0) 'NA'] [(1) 'Blue']
3 [(1,0) 'NA'] [(1,1) 'NA'] [(1) 'Blue']
4 [(0,0) 'NA'] [(0,1) 'Green'] [(1) 'Blue']
where '
NA
' means '
Not Available
'
See:
SQL Server 2019 | db<>fiddle[
^]