2

below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated.

SELECT DISTINCT (t3.field1), t3.field2,
       LISTAGG(t3.field3, ', ') WITHIN GROUP (ORDER BY t3.field3),
       SUM(t2.field1), SUM(t2.field2)
  FROM Table1 t1, Table2 t2, Table3 t3
 WHERE t1.id = t2.idoft1
   AND t2.id = t3.idoft2
GROUP BY t3.field1, t3.field2;

UPDATE:

Here is SQLFiddle version.

3

You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website:

WITH a as ( SELECT t3_field1,
                   t3_field2,
                   LTRIM(MAX(SYS_CONNECT_BY_PATH(field3,', '))
                       KEEP (DENSE_RANK LAST ORDER BY curr),', ') AS agg
            FROM   (SELECT field1 t3_field1,
                           field2 t3_field2,
                           field3,
                           ROW_NUMBER() 
                             OVER (PARTITION BY field1, field2 ORDER BY field3)
                             AS curr,
                           ROW_NUMBER() 
                             OVER (PARTITION BY field1, field2 ORDER BY field3) -1 
                             AS prev
                    FROM   Table3)
            GROUP BY t3_field1, t3_field2
            CONNECT BY prev = PRIOR curr 
                 AND t3_field1 = PRIOR t3_field1 
                 AND t3_field2 = PRIOR t3_field2
            START WITH curr = 1 ),
     s as ( SELECT t3.field1 t3_field1,
                   t3.field2 t3_field2,
                   SUM(t2.field1) sum_t2_field1,
                   SUM(t2.field2) sum_t2_field2
            FROM Table1 t1, Table2 t2, Table3 t3
            WHERE t1.id = t2.idoft1 AND t2.id = t3.idoft2 AND t1.id = 3
            GROUP BY t3.field1, t3.field2 )
SELECT * FROM a JOIN s USING (t3_field1,t3_field2);

Results:

| T3_FIELD1 | T3_FIELD2 |                                             AGG | SUM_T2_FIELD1 | SUM_T2_FIELD2 |
|-----------|-----------|-------------------------------------------------|---------------|---------------|
|      asdf |      test | and_one_more_text, some_another_text, text_here |          1998 |           210 |
|     asdf2 |      test |                           text_in_another_group |           666 |            70 |

SQLFiddle here

  • Thanks for the answer, this query works great! So, the role of WITH() command which consists of two parts (subqueries) is a "prerequisite" and one can use such methods for building cursors plsql packages? – Dozent Jun 9 '15 at 12:42
  • 1
    you don't need to use subquery factoring (aka 'with' clause), I just found it convenient from the starting point you gave. It's quite possible you could do this with a single query rather than two as I have here. Not sure what any of this has to do with cursors or PL/SQL - this is a pure SQL question. – Jack Douglas Jun 9 '15 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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