Using IN clause with List agg. function

Question

is there a way to use "IN" clause with the "List" aggregate function, like in this example:

SELECT 
  FirstName,
  LastName,
  CASE 
    WHEN 1 IN LIST(ID) THEN 'Admin'
    WHEN 2 IN LIST(ID) THEN 'Moderator'
    WHEN 3 IN LIST(ID) THEN 'Owner'
  ELSE
    String(FirstName, ' ', LastName)
  END as Description
FROM Users
Group By FirstName, LastName;

and if not what is the work around for that?

Answer 1

If I uderstant your query corectly, this should net the result you want:

SELECT
  FirstName,
  LastName,
  CASE MIN(ID)
    WHEN 1 THEN 'Admin'
    WHEN 2 THEN 'Moderator'
    WHEN 3 THEN 'Owner'
  ELSE
    String(FirstName, ' ', LastName)
  END as Description
FROM Users
Group By FirstName, LastName;

Alternative but not pretty workaround could be CASE WHEN ','+LIST(ID)+',' LIKE '%,1,%' THEN ...

Answer 2

The only solution I found is to split the list again using sa_split_list like this:

SELECT 
  FirstName,
  LastName,
  LIST(ID) as lst,
  CASE 
    WHEN 1 IN (SELECT row_value from sa_split_list(lst)) THEN 'Admin'
    WHEN 2 IN (SELECT row_value from sa_split_list(lst)) THEN 'Moderator'
    WHEN 3 IN (SELECT row_value from sa_split_list(lst)) THEN 'Owner'
  ELSE
    String(FirstName, ' ', LastName)
  END as Description
FROM Users
Group By FirstName, LastName;

or

SELECT 
  FirstName,
  LastName,
  LIST(ID) as lst,
  CASE 
    WHEN Exists(SELECT 1 from sa_split_list(lst) where row_value = 1) THEN 'Admin'
    WHEN Exists(SELECT 1 from sa_split_list(lst) where row_value = 2) THEN 'Moderator'
    WHEN Exists(SELECT 1 from sa_split_list(lst) where row_value = 3) THEN 'Owner'
  ELSE
    String(FirstName, ' ', LastName)
  END as Description
FROM Users
Group By FirstName, LastName;

and that did the job, i still think the performance will be bad for big queries...