Aggregating values into a table type within a GROUP BY query

Question

Suppose you have a table (in Oracle):

CREATE TABLE CUSTOMER
(
  customer_id NUMBER,
  gender      CHAR(1)
);

And suppose you have a table type:

CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

Is it possible to write a GROUP BY query such that, for each group, that group's primary key fields are stored in a NUMBER_TABLE_TYPE? For example:

DECLARE
  CURSOR gender_cursor IS
    SELECT
      gender,
      /* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
      SOME_MAGICAL_AGGREGATE_FUNCTION(customer_id) AS customer_ids
    FROM
      CUSTOMER
    GROUP BY
      gender;

  customer_ids NUMBER_TABLE_TYPE;
BEGIN
  FOR gender IN gender_cursor LOOP
    customer_ids := gender.customer_ids;
    FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
      dbms_output.put_line(customer_ids(i));
    END LOOP;
  END LOOP;
END;

I know that this should be accomplished by having two cursors (one for the gender group, and another that queries each group's customers). I am just curious to see if this can be done. :)

Answer 1

Two ways to accomplish this. Firstly, with analytics to get the count of all the records for that gender on each row.

DECLARE
  CURSOR gender_cursor IS
    SELECT gender, customer_id, count(*) over (partition by gender) cnt_in_gender,
    FROM   CUSTOMER
    ORDER BY gender;
  v_prev_gender varchar2(1) := '?';
BEGIN
  FOR gender IN gender_cursor LOOP
      IF gender.gender != v_prev gender then
        dbms_output.put_line('You will now see '||gender.cnt_in_gender);
        v_prev_gender := gender.gender);
      END IF;
      dbms_output.put_line(gender.customer_ids);
  END LOOP;
END;

Secondly, and closer to your request

DECLARE
  CURSOR gender_cursor IS
    SELECT
      gender,
      /* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
      CAST(COLLECT(customer_id) AS NUMBER_TABLE_TYPE) AS customer_ids
    FROM
      CUSTOMER
    GROUP BY
      gender;

  customer_ids NUMBER_TABLE_TYPE;
BEGIN
  FOR gender IN gender_cursor LOOP
    customer_ids := gender.customer_ids;
    FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
      dbms_output.put_line(customer_ids(i));
    END LOOP;
  END LOOP;
END;