Sql Server Weird CASE Statement

Question

I am attempting to do something, but I am not sure if it is possible. I don't really know how to look up something like this, so I'm asking a question here.

Say this is my table:

Name | Group
-----+--------
John | Alpha
Dave | Alpha
Dave | Bravo
Alex | Bravo

I want to do something like this:

SELECT TOP 1 CASE
               WHEN Group = 'Alpha' THEN 1
               WHEN Group = 'Bravo' THEN 2
               WHEN Group = 'Alpha' AND
                    Group = 'Bravo' THEN 3
               ELSE 0
             END AS Rank
FROM table
WHERE Name = 'Dave'

I understand why this won't work, but this was the best way that I could explain what I am trying to do. Basically, I just need to know when one person is a part of both groups. Does anyone have any ideas that I could use?

https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list — Salman A, Sep 13 '18 at 15:49
This seems to be an [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). What do you intend to do with the value of 3 (or 2 or 1) for Dave? What will happen when Dave belongs to an additional group? Or changes Alpha to Delta? — SMor, Sep 13 '18 at 15:56
There is only ever going to be Alpha and Bravo. I just need to know if someone is in Alpha, Bravo, or Both. Having problems with the BOTH part. — A.Stekl, Sep 13 '18 at 15:59
Please check my updated answer, i think you've over complicated your requirement. — Tanner, Sep 13 '18 at 16:10

Tanner · Accepted Answer · 2018-09-13T16:08:37.223

3

You should create a column to hold the values you want to sum and sum them, probably easiest to do this via a subquery:

Select Name, SUM(Val) as Rank
FROM (SELECT Name, CASE WHEN Group = 'Alpha' THEN 1
                        WHEN Group = 'Bravo' THEN 2
                        ELSE 0 END AS Val
      FROM table
      WHERE Name = 'Dave') T
GROUP BY Name

You can add TOP 1 and ORDER BY SUM(Val) to get the top ranked row if required.

After reading your comment, it could be simplified further to:

Select Name, COUNT([GROUP]) GroupCount
FROM table         
GROUP BY Name
HAVING COUNT([GROUP]) > 1

That will simply return all names where they have more than 1 group.

edited Sep 13 '18 at 16:08

answered Sep 13 '18 at 15:58

Tanner

22,205
9
65
83

2

This feels like it's heading towards a bitmask as a solution. `Delta` would equal `4`, `Gamma` would equal `8`, and so on... Then each value could be pulled back by [looking at the corresponding bit of the integer generated here](http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131948). Even though OP is really just looking at `alpha` and `bravo` this scales nicely as a result. – JNevill Sep 13 '18 at 16:00
1

@JNevill yeh, could work, but OP did state in comments it's only the 2 values: Alpha & Bravo – Tanner Sep 13 '18 at 16:05
1

@JNevill in fact his last comment makes the problem much simpler, he just needs grouping with a having clause. – Tanner Sep 13 '18 at 16:10

Sql Server Weird CASE Statement

1 Answers1