1

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,
4
  • It seems your GROUP BY columns should be NAME, AN, COD. Try leaving only those in GROUP BY and aggregating the rest (i.e. CANT and TIP).
    – Andriy M
    Aug 12, 2015 at 13:50
  • I edited my post
    – WDrgn
    Aug 12, 2015 at 14:34
  • 2
    And why are you aggregating A10.CANT like that, using a correlated subquery and referencing ANEXA10_ROWS for the second time? Have you tried just SUM(A10.CANT) AS CANT?
    – Andriy M
    Aug 12, 2015 at 14:48
  • yah, I was dizzy :), I used LISTAGG for TIP, SUM(A10.CANT) for CANT and GROUP BY RAP.NAME, ses_rap.an, CHT.ID, A10R.COD. Thanks!
    – WDrgn
    Aug 12, 2015 at 14:55

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.