oracle - Oracle Count 基于 key 的动态组数

Question

考虑表格：

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

我想values根据id列来计算组。结果将如下所示：

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

请注意，列的基数value是动态的。

我尝试过使用一些子查询进行旋转，但我不确定我是否以正确的方式进行。

我感谢提供的任何帮助。

标签： oraclecountpivotgroupingclustering-key

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

小提琴