A = 1 else 0 if B satisfy two conditions in ms sql server(azure data studio)

Question

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.

Condition

1. if ColB has only 1 then online
2. if ColB has only 2 then offline
3. if ColB has both 1 and 2 then both
4. if ColB does not have 1 or 2 then none

ColA	ColB
A	1
A	2
A	1
B	1
B	1
C	3
D	2
D	2

The result should be like this

ColA	distinguish
A	both
B	online
C	none
D	offline

Thanks!

Answer 1

Looks like you just need conditional COUNT

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;

Answer 2

This is just another way to skin the same cat, using a conditional COUNT(DISTINCT) and a conditional MAX():

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
;