如何在Oracle中使用多个列聚合多个行?

时间:2022-01-26 04:38:48

I have a table (named 'games') that has three columns: weather, sports and customer

我有一张桌子(名为“游戏”),有三列:天气,运动和顾客

weather sports       customer
sun     volleyball   Randy 
sun     volleyball   Lau
sun     gym          Ryan
sun     gym          Rachel

The table to be

表是

weather sports       customer
sun     volleyball   Randy, Lau
sun     gym          Ryan, Rachel

I have used following LISTAGG command however it gives me error saying 'not a group by expression'

我使用了以下LISTAGG命令然而它给我错误说“不是表达式组”

SELECT
    weather, sports,
    LISTAGG(customer, ',') WITHIN GROUP (ORDER BY sports) "Customer"
    FROM games
    GROUP BY customer;

1 个解决方案

#1


2  

The GROUP BY needs to contain the unaggregated columns. They define each row in the result set:

GROUP BY需要包含未聚合的列。它们定义结果集中的每一行:

SELECT weather, sports,
       LISTAGG(customer, ',') WITHIN GROUP (ORDER BY sports) as Customers
FROM games
GROUP BY weather, sports;

#1


2  

The GROUP BY needs to contain the unaggregated columns. They define each row in the result set:

GROUP BY需要包含未聚合的列。它们定义结果集中的每一行:

SELECT weather, sports,
       LISTAGG(customer, ',') WITHIN GROUP (ORDER BY sports) as Customers
FROM games
GROUP BY weather, sports;