SQL - Multiple Values comma separated when using G

Question

This question already has answers here:

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

Closed 5 years ago.

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.

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

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

Thank you.

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

Answer 2

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

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

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

Answer 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:

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.