Click here to Skip to main content
14,977,931 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[^]
   
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