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

How to do the Same in SQL Server – Haseeb Asif Sep 28 '12 at 11:32 — Haseeb Asif, Sep 28 '12 at 11:32

score 21 · Answer 1 · answered Apr 20 '12 at 20:06

21

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

answered Apr 20 '12 at 20:06

ScrappyDev

2,307
8
40
60

3

LISTAGG is not availble in 10g. – Jon Heller Apr 21 '12 at 01:14

score 3 · Answer 2 · answered Nov 12 '12 at 15:11

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.

answered Nov 12 '12 at 15:11

Chris Gurley

31
2

score 3 · Accepted Answer · answered Oct 24 '08 at 17:44

3

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.

answered Oct 24 '08 at 17:44

ConcernedOfTunbridgeWells

64,444
15
143
197

6

-1 because this answer is useless on it's own. Please consider improving your answer by summarizing the content in the link in case the link becomes invalid in the future. – axiopisty Jun 19 '17 at 21:07

score 0 · Answer 4 · answered Oct 24 '08 at 18:52

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.

SQL - Multiple Values comma separated when using GROUP BY

4 Answers4

Linked