0

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!

3

2 Answers 2

2

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;
4
  • What if the ColB is not a numeric value but a string? are there any other ways?
    – ItsKJ
    Jul 7, 2022 at 14:05
  • Just put the values in quotes, see edit Jul 7, 2022 at 14:14
  • What is not correct about it? Possibly you need to define your conditions better: what happens if there are other values? Jul 7, 2022 at 14:31
  • There is no '1' for C. I think that part is only mistake otherwise it seems working properly
    – ItsKJ
    Jul 7, 2022 at 14:43
1

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.