Optimize join – group by Oracle SQL filter

group byjoin;oracle

I have a few tables:

CHEST:
    id  fk_id_rap   fk_id_ses_rap
    1   1           1
    2   2           1
    3   3           1

SES_RAP:
id  an
1   2015
2   2016

RAP:
id  name
1   rap1
2   rap2
3   rap3

anexa10
id  fk_id_cht
10  1
11  1
12  2
13  2

anexa10_rows        
id  cod         cant  tip   fk_id_anexa10
1   01 01 01    45    r2    10
2   01 01 01    34    r3    11
3   00 00 00    10    r5    12
4   01 03 04    70    r6    13

If i try something like this:

SELECT RAP.name, ses_rap.an, A10.cod,
            (SELECT SUM (COALESCE (A10.CANT, 0))
               FROM ANEXA10_ROWS A10B
              WHERE A10B.FK_ID_ANEXA10 = A10.ID AND 
                    A10B.COD = A10T3.COD)
               AS CANT,
        MIN(A10R.TIP),
       FROM CHEST cht
            INNER JOIN rap rap
               ON cht.fk_id_rap = rap.id
            INNER JOIN SES_RAP ses_rap
               ON ses_rap.id = CHT.FK_ID_SES_RAP
            FULL OUTER JOIN ANEXA10 A10
               ON A10R.FK_ID_CHT = CHT.ID
            FULL OUTER JOIN ANEXA10_ROWS A10R
               ON A10R.FK_ID_ANEXA10 = A10.ID
      WHERE SES_RAP.AN = '2015' AND A10.COD IS NOT NULL
   GROUP BY RAP.NAME,
            ses_rap.an,
            CHT.ID,
            A10R.COD,
            A10R.CANT,
            A10R.TIP
   ORDER BY RAP.NAME

The result is:

NAME    AN      COD        CANT TIP
rap1    2015    01 01 01   45   r2
rap1    2015    01 01 01   34   r3
rap2    2015    00 00 00   10   r5
rap2    2015    01 03 04   70   r6

How can I get the result?

NAME    AN      COD        CANT TIP
rap1    2015    01 01 01    79  r2r3
rap2    2015    00 00 00    10  r5
rap2    2015    01 03 04    70  r6

—————-EDIT1:
if I remove A10R.CANT from GROUP BY it gives me that it's not a GROUP BY expression on 

(SELECT SUM (COALESCE (A10.CANT, 0))
                   FROM ANEXA10_ROWS A10B
                  WHERE A10B.FK_ID_ANEXA10 = A10.ID AND 
                        A10B.COD = A10T3.COD)
                   AS CANT,

Best Answer

Check https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle. Here the LISTAGG function is explained. This functionn delivers what you need.

Related Question