This question already has answers here:
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.
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.
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
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.
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.
