SQL - Multiple Values comma separated when using GROUP BY

Question

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, need plain SQL or ORACLE equivelant. 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

Thx.

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

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.