Check if all values in a column falls into a specific group

I have the following table:

+-------+
| Token |
+=======+
| A     |
+-------+
| B     |
+-------+
| B     |
+-------+
| C     |
+-------+
| A     |
+-------+
| X     |
+-------+

and I want to check ALL values of the column ‘Token’ and return a flag string to designate what condition is met:

• when all records are in group (A, B) –> return 'Condition1',

  • examples:

    AABAAB: true
    AAAAAA: false
    BBBBBB: false
    AABBAC: false
    

• when all records are in group (A, B, C) –> return 'Condition2'

  • examples:

    AABBAC: true
    AABAAB: false
    AAAAAA: false
    BBBBBB: false
    AXBBAC: false
    

so I am basically looking for a general SELECT statement that looks like:

      select case when exists ( select ....
                                  from test_table
                                 where  ....)  -- the SQL Statement that checks if Condition1 is met
                  then 'Condition1'
                  when exists ( select ....
                                  from test_table
                                 where ....) -- the SQL Statement that checks if Condition1 is met
                  then 'Condition1'
                  else 'NOTHING'
             end condition_met
        from dual;

or any other form of SQL that provide the desired results.

Answer

You can use LISTAGG() aggregate function to get all the distinct values of Token as a comma separated list and check the result in a CASE expression:

WITH cte AS (
  SELECT LISTAGG(Token, ',') WITHIN GROUP (ORDER BY Token) Tokens
  FROM (SELECT DISTINCT Token FROM tablename) t
)
SELECT CASE Tokens
  WHEN 'A,B' THEN 'Condition1'
  WHEN 'A,B,C' THEN 'Condition2'
  ELSE 'Nothing'
END condition_met  
FROM cte

See the demo.

Note that in recent versions of Oracle you can include DISTINCT inside LISTAGG():

WITH cte AS (
  SELECT LISTAGG(DISTINCT Token, ',') WITHIN GROUP (ORDER BY Token) Tokens
  FROM tablename
)
....................................