Oracle Select Query with JOINS

Question

I have the below Oracle SQL Query and it works fine

 Select Cage.BARCODE Cg, CagePen.BARCODE Pen
 FROM LOCATION_ENTITY CagePen
 LEFT JOIN LOCATION_ENTITY Cage ON SUBSTR(CagePen.FULL_LOCATION, -6)=Cage.BARCODE
 WHERE CagePen.FULL_LOCATION = 'Current'

I get the response back like

      CG    PEN
  CGE100    CP4265
  CGE100    CP4264
  CGE101    CP4266
  CGE101    CP4267
  CGE102    CP4268
  CGE102    CP4269

Now I am trying to consolidate the response where CG is repeated

   CG            PEN
 CGE100   CP4265,   CP4264
 CGE101   CP4266,   CP4267
 CGE102   CP4268,   CP4269

Should this query be using agg()

Possible duplicate of [SQL Query to concatenate column values from multiple rows in Oracle](https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) — Aaron Dietz, Aug 22 '18 at 16:27
Looks like the question is not really about the joins but about aggregating the results into a comma-separated format. — William Robertson, Aug 22 '18 at 17:37

score 3 · Accepted Answer · answered Aug 22 '18 at 16:27

You are looking for listagg()

SELECT Cage.BARCODE as Cg, LISTAGG(CagePen.BARCODE, ',') WITHIN GROUP (ORDER BY CagePen.BARCODE) as Pens
FROM LOCATION_ENTITY CagePen LEFT JOIN
     LOCATION_ENTITY Cage 
     ON SUBSTR(CagePen.FULL_LOCATION, -6) = Cage.BARCODE
WHERE CagePen.FULL_LOCATION = 'Current'
GROUP BY Cage.BARCODE;

Oracle Select Query with JOINS

1 Answers1