All we need is an easy explanation of the problem, so here it is.
I am trying to get the distinguish column depending on another column’s value.
I was trying to approach with CASE function but could not get exactly what I wanted.
- if ColB has only 1 then online
- if ColB has only 2 then offline
- if ColB has both 1 and 2 then both
- if ColB does not have 1 or 2 then none
The result should be like this
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Looks like you just need conditional
SELECT t.ColA, CASE WHEN COUNT(CASE WHEN t.ColB = '1' THEN 1 END) > 0 THEN CASE WHEN COUNT(CASE WHEN t.ColB = '2' THEN 1 END) > 0 THEN 'both' ELSE 'online' END ELSE CASE WHEN COUNT(CASE WHEN t.ColB = '2' THEN 1 END) > 0 THEN 'offline' ELSE 'none' END END FROM YourTable t GROUP BY t.ColA;
This is just another way to skin the same cat, using a conditional
COUNT(DISTINCT) and a conditional
SELECT ColA , distinguish = CASE COUNT(DISTINCT CASE WHEN ColB IN ('1', '2') THEN ColB END) WHEN 0 THEN 'none' WHEN 2 THEN 'both' ELSE CASE MAX(CASE WHEN ColB IN ('1', '2') THEN ColB END) WHEN '1' THEN 'online' ELSE 'offline' END END FROM dbo.YourTable GROUP BY ColA ;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂