Click here to Skip to main content
15,436,765 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Could some one please help me to write query to get below output.

Conditon :- IF Status1=0 and Status2 =1 then 'Red' , IF status3=0 and status4=1 then 'Green',IF Status5=1 then 'Blue' need to show in StatusColor column for every ID


select ID,Status1,status2,status3,status4 from StatusMaster


Data					
ID	Status1	status2	status3	status4	Status5
1	0	1	1	0	1
2	1	1	0	0	1
3	1	0	1	1	1
4	0	0	0	1	1


Output	
ID	StatusColor
1	Red,Green,Blue
2	Red
3	Green
4	Red,Green


What I have tried:

I have tried doing with CASE statement but not working as expected
Posted
Updated 22-Mar-21 3:07am

1 solution

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[^]
 
Share this answer
 
v2
Comments
Pro86 22-Mar-21 9:11am    
what if first and second condition is true , then I need to show both 'Red' and 'Green' in comma separated
Maciej Los 22-Mar-21 9:16am    
What's database?
Pro86 22-Mar-21 9:18am    
MS SQL Server
Maciej Los 22-Mar-21 9:23am    
Then use CONCAT
Pro86 23-Mar-21 5:54am    
could you please give me example

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900