Oracle Count dynamic number of groups based on key

Question

Considere the table:

+--------+-------+
| id     | value |
+--------+-------+
| 1      |   A   |
| 1      |   B   |
| 2      |   A   |
| 2      |   B   |
| 3      |   A   |
| 3      |   B   |
| 3      |   C   |
| 4      |   A   |
+--------+-------+

and I want to count the group of values based on the id column. The result will be like:

+--------+---------+
| count  | value   |
+--------+---------+
| 2      |   A B   |
| 1      |   A B C |
| 1      |   A     |
+--------+---------+    

Note that the cardinality of column value is dynamic.

I've tried pivoting with some subqueries but I'm not sure if I'm going in the correct way.

I appreciate any help given.

Answer 1

with src_data as (
    select 1 f1, 'A' f2 from dual
    union all
    select 1, 'B' from dual
    union all
    select 2, 'B' from dual
    union all
    select 2, 'A' from dual
    union all
    select 3, 'A' from dual
    union all
    select 3, 'B' from dual
    union all
    select 3, 'C' from dual
    union all
    select 4, 'A' from dual
)
select count(1) cnt, value
from (
    select f1, listagg(f2, ' ') within group(order by f2) value
    from src_data
    group by f1
)
group by value
order by cnt desc, value

fiddle