SQL - 使用GROUP BY [duplicate]时以逗号分隔的多个值

This question already has an answer here:

这个问题在这里已有答案:

How can I combine multiple rows into a comma-delimited list in Oracle? [duplicate] 11 answers

如何将多行组合到Oracle中以逗号分隔的列表中? [重复] 11个答案

I have data that looks like

我的数据看起来像

CUSTOMER,  CUSTOMER_ID, PRODUCT
ABC INC    1            XYX
ABC INC    1            ZZZ
DEF CO     2            XYX
DEF CO     2            ZZZ
DEF CO     2            WWW
GHI LLC    3            ZYX

I'd like to write a query that'd make the data look like this:

我想写一个查询,使数据看起来像这样:

CUSTOMER, CUSTOMER_ID, PRODUCTS
ABC INC   1            XYX, ZZZ
DEF CO    2            XYX, ZZZ, WWW
GHI LLC   3            ZYX

Using Oracle 10g if helps. I saw something that would work using MYSQL, but I need a plain SQL or ORACLE equivalent. I've also seen examples of stored procs that could be made, however, I cannot use a stored proc with the product i'm using.

如果有帮助,请使用Oracle 10g。我看到了一些可以使用MYSQL工作的东西,但我需要一个简单的SQL或ORACLE等价物。我也看过可以制作的存储过程的例子,但是,我不能使用我正在使用的产品的存储过程。

Here's how'd it work in MySQL if I were using it

如果我使用它,它在MySQL中是如何工作的

SELECT CUSTOMER, 
       CUSTOMER_ID, 
       GROUP_CONCAT( PRODUCT ) 
FROM MAGIC_TABLE 
GROUP BY CUSTOMER, CUSTOMER_ID

Thank you.

4 个解决方案

#1

This link refers to a number of examples of different ways to do this on Oracle. See if there's something there that you have permissions on your database to do.

此链接指的是在Oracle上执行此操作的不同方法的大量示例。看看你的数据库是否有权限。

#2

I think LISTAGG is the best aggregate group by function to use in this situation:

我认为LISTAGG是在这种情况下使用的最佳聚合组:

  SELECT CUSTOMER, CUSTOMER_ID,
         LISTAGG(PRODUCT, ', ') WITHIN GROUP (ORDER BY PRODUCT)
    FROM SOME_TABLE
GROUP BY CUSTOMER, CUSTOMER_ID
ORDER BY 1, 2

#3

The oracle user function 'wm_concat' works the same way as LISTAGG except you cannot specify a delimiter ',' by default or a sort order. It is however compatible with 10g.

oracle用户函数'wm_concat'的工作方式与LISTAGG相同,但默认情况下不能指定分隔符','或排序顺序。然而它与10g兼容。

#4

Thanks Nigel,

My SQL is not as elegant as could be, but I needed a solution that required SQL only, not PLSQL or TSQL, so it ended up looking like this:

我的SQL不是那么优雅,但我需要一个只需要SQL而不是PLSQL或TSQL的解决方案,所以最终看起来像这样:

SELECT   CUSTOMER, CUSTOMER_ID, COUNT(PRODUCT) PROD_COUNT, 
         RTRIM( 
            XMLAGG( XMLELEMENT (C, PRODUCT || ',') ORDER BY PRODUCT
).EXTRACT ('//text()'), ',' 
         ) AS PRODUCTS FROM     (
         SELECT   DISTINCT CUSTOMER, CUSTOMER_ID, PRODUCT
         FROM     MAGIC_TABLE
         ) GROUP BY CUSTOMER, CUSTOMER_ID ORDER BY 1 , 2

Still not exactly sure what the XML functions do exactly, but I'll dig in when the need arrises.

仍然不完全确定XML函数究竟做了什么,但是当需要时我会深入研究。

#1