Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
select t.a, x.column_value as b
from t
outer apply t.b x


A
B


1
ff

1
ee

1
ee

2
ee


WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
select t.a, t.b.count()
from t
ORA-00904: "T"."B"."COUNT": invalid identifier

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
select t.a, t.b.first()
from t
ORA-00904: "T"."B"."FIRST": invalid identifier

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
select t.a, t.b.exist(1)
from t
ORA-00904: "T"."B"."EXIST": invalid identifier