Multiply records

In one of my task, I needed to multiply some records in a table, so that they can have more chances to be picked up in a random draw. I did it in SQL Server, so I'll show it in SQL. It's similar in Oracle.
What I need to do is to Cartesian join the target records with a table with a certain number of records.

Create a test table and insert some records.

create table test_multiply( id int, type varchar(10), comments varchar(100) )
go
insert into test_multiply values ( 1, 'single', 'This row will show only once' )
insert into test_multiply values ( 2, 'times 10', 'This row will repeat 10 times' )
go

Now we're to multiply the second record 10 times, we join the records with a table contains 9 records.

insert into test_multiply
select a.* from test_multiply a
inner join ( select top 9 id from sysobjects ) x on 1=1
where a.type = 'times 10'
go
(9 rows affected)

Check the result.

select id, type, count(*) cnt from test_multiply group by id, type
go
id type cnt
----------- ---------- -----------
1 single 1
2 times 10 10

Similar in Oracle.

insert into test_multiply
select a.* from test_multiply a
inner join ( select 1 from all_objects where rownum < 1="1" type =" 'times">

PowerMultiSet in Oracle 10g

In one of my task, I needed to find all the possible combinations of a few strings. By searching the functions in Oracle, I found an interesting and powerful function: PowerMultiSet.

Oracle says the function returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table.

The submultisets are just the combinations of all elements (you're guaranteed :-) ) and will have 2ⁿ-1 sets.

Let see with an example:

create or replace type NUM_LIST_TYPE is table of number;
/
create or replace type NUM_LIST_LIST_TYPE is table of NUM_LIST_TYPE;
/

select NUM_LIST_TYPE( 1, 2, 3 ) base_set from dual;

BASE_SET
-----------------------
NUM_LIST_TYPE(1, 2, 3)

It shows three elements in the set: 1, 2 and 3. The run PowerMultiSet.

select cast( powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) as NUM_LIST_LIST_TYPE ) power_set from dual;

POWER_SET
--------------------------------------------------------------------------------
NUM_LIST_LIST_TYPE(NUM_LIST_TYPE(1), NUM_LIST_TYPE(2), NUM_LIST_TYPE(1, 2), NUM_
LIST_TYPE(3), NUM_LIST_TYPE(1, 3), NUM_LIST_TYPE(2, 3), NUM_LIST_TYPE(1, 2, 3))

Let's make it easy to read:

1
2
1 2
3
1 3
2 3
1 2 3

However, you have to create the type that is nested table of nested tables and cast the result into it. Otherwise, you'll get an error.

select powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) power_set from dual;

ORA-22833: Must cast a transient type to a persistent type

The number of elements in the input nested table cannot exceed 32. The number of submulties in such case would be 2³² -1, that's 4 gig.

String aggregate in Oracle

You may have known aggregate functions well, for example, sum, count, max, min, etc. How about add up strings? After searching on the web for a while, I found a function (See the code at the bottom).
The main function is STRING_AGG, which you can use the same way as other aggregate functions. Example:

    select employee_id, string_agg( job_id ) job_list from job_history group by employee_id;

Here's the output in SQL Plus:

      EMPLOYEE_ID JOB_LIST
  ----------- ----------------------------------------
          101 AC_ACCOUNT,AC_MGR
          102 IT_PROG
          114 ST_CLERK
          122 ST_CLERK
          176 SA_REP,SA_MAN
          200 AD_ASST,AC_ACCOUNT
          201 MK_REP

  7 rows selected.

The delimiter by default is comma (,), and is controlled by a store package STRING_AGG_CONTROL. To change it, you can use:

     begin string_agg_control.g_delim := '|'; end;
   /

Then run the sql again:

    select employee_id, string_agg( job_id ) job_list from job_history group by employee_id;
  EMPLOYEE_ID JOB_LIST
  ----------- ----------------------------------------
          101 AC_ACCOUNT|AC_MGR
          102 IT_PROG
          114 ST_CLERK
          122 ST_CLERK
          176 SA_REP|SA_MAN
          200 AD_ASST|AC_ACCOUNT
          201 MK_REP

  7 rows selected.

The function is implemented using an Oracle user defined type with the help of User-Defined Aggregates Interface. You can find more information in this doc.
Sounds perfect? Not that much -- you cannot order the strings. That means the source strings appear in the aggregate string randomly.
The script is attached:

create or replace package string_agg_control IS
  g_delim  varchar2(100) := ',';
end;
/

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),
  g_delim   VARCHAR2(100),  -- Delimiter to seperate the strings

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg( NULL, string_agg_control.g_delim );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || self.g_delim || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, self.g_delim ), self.g_delim );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || self.g_delim || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS