Collecting into VARRAY inside SQL statement

Question

I have the following SQL query :

SELECT ID, OC_YEAR FROM ACCOUNTS;

This query returns the following resultset:

       ID       |     OC_YEAR          
------------------------------------    
       10            2006
       10            2007
       10            2008
       11            2006
       11            2008

On the other hand I have a custom type

 create TYPE IDS AS VARRAY(10) OF NUMBER(5);

And finally I have a function that recieves 2 parameters of type IDS and NUMBER. Is there a way I can collect ID columns into an IDS variable grouping them under the OC_YEAR column and send these two arguments to my function. To make it clearer I need to group the resultset of the above query like this:

(10,11):2006
(10):2007
(10,11):2008

Not exactly but something similar to GROUP_CONCAT in MySQL.

As I said, I'm not looking for an exact equivalent of GROUP_CONCAT — Mikayil Abdullayev, Nov 29 '13 at 13:41

score 2 · Answer 1 · answered Nov 29 '13 at 13:36

2

Oracle 11.2 introduced function LISTAGG, which does the same like GROUP_CONCAT. If you don't have Oracle 11.2 available you have to encapsulate it in a function.

answered Nov 29 '13 at 13:36

Wernfried Domscheit

54,457
9
76
110

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Duplicate of this one:

If you have Oracle 10G:

CREATE OR REPLACE FUNCTION GET_COMMA_SEPARATED_VALUE ( INPUT_VAL IN NUMBER )
    RETURN VARCHAR2
IS
    RETURN_TEXT VARCHAR2 ( 10000 ) := NULL;
BEGIN
    FOR X IN ( SELECT
                  ID
            FROM
                  ACCOUNTS
            WHERE
                  OC_YEAR = INPUT_VAL )
    LOOP
        RETURN_TEXT :=
               RETURN_TEXT
            || ','
            || X.ID;
    END LOOP;

    RETURN LTRIM ( RETURN_TEXT,
                ',' );
END;
/

So, you can do like:

SELECT
      GET_COMMA_SEPARATED_VALUE ( ID ),
      OC_YEAR
FROM
      ACCOUNTS;

If you have got oracle 11g, you can use listagg :

SELECT
      LISTAGG ( OC_YEAR,
              ', ' )
      WITHIN GROUP (ORDER BY ID),
      OC_YEAR
FROM
      ACCOUNTS
GROUP BY
      OC_YEAR;

I think there is a mistake in your 10g Code. Shouldn't the function expect the `OC_YEAR` as a parameter? — Armunin, Nov 29 '13 at 13:40

Collecting into VARRAY inside SQL statement

2 Answers2