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.
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