将两行组合在甲骨文中

Question

我在甲骨文里有一张桌子，已经从一些大的桌子上取下来了。

+---------------+------------+
| shop   number | Model Type |  
+---------------+------------+
| 1             | Mod-1      |  
+---------------+------------+
| 1             | Mod-2      |  
+---------------+------------+
| 2             | Mod-3      | 
+---------------+------------+
| 2             | Mod-3      | 
+---------------+------------+
| 3             | Mod-4      |
+---------------+------------+
| 3             | Mod-5      |
+---------------+------------+
| 4             | Mod-1      |
+---------------+------------+
| 4             | Mod-2      | 
+---------------+------------+
| 4             | Mod-6      | 
+---------------+------------+
| 4             | Mod-3      |
+---------------+------------+
| 5             | Mod-1      |
+---------------+------------+

实际上，这是基于一个地方的商店对行进行梳理，并在此基础上生成两个不同的列，结果应该如下所示

+---------------+---------------------------------+-----------+
| shop   Number | Shop Type                       | Shop Type |
+---------------+---------------------------------+-----------+
| 1             | Mod-1 & Mod-2                    | Mix       |
+---------------+---------------------------------+-----------+
| 2             | Mod-3                           | Mod-3     |
+---------------+---------------------------------+-----------+
| 3             | Mod-4 & Mod-5                   | Mix       |
+---------------+---------------------------------+-----------+
| 4             | Mod-1 & Mod-2   & Mod-3 & Mod-6 | Mix       |
+---------------+---------------------------------+-----------+
| 5             | Mod-1                           | Mod-1     |
+---------------+---------------------------------+-----------+

不知道如何处理this.Please帮助.

Answer 1

可以使用以下SQL

select shop_number,
listagg(distinct model_type, ' & ') within group (order by model_type) shop_type,
decode(count(distinct model_type), 1, max(model_type), 'Mix') shop_type
from mytable
group by shop_number;

注意，distinct关键字在listagg()中是在19c中添加的。如果使用旧版本，则需要在使用listagg()之前进行区分。

[医]小提琴