Oracle - SQL - Evenly dividing a range of Data using NTILE, MIN, and MAX

Problem: Over a widely dispersed set of IDs, create 10 evenly divided ranges each in terms of count per group. Determine the ID range for each group.
Practical: We needed to scrub a bunch of data in parallel streams. The easist answer was to create 10 simultaneous UPDATEs, but we didn't want the updates stepping on each other.
Solution: Use NTILE to create 10 evenly "divided" temporary groups [from a 'rowcount' standpoint]. Use MIN and MAX against each temporary group to determine the starting and ending points. Query:

SELECT 
   Quartile,
   MIN("ID") "StartingID",
   MAX("ID") "EndingID",
   COUNT(*) "CountPerGroup"
FROM
   ( 
      SELECT 
         a.contract_payment_id "ID",
         NTILE(10) OVER(ORDER BY a.contract_payment_id) AS quartile
      FROM   
         contract_payment a 
      WHERE
         a.transaction_type_code = 'CC' and 
         a.credit_card_type_code = 'VISA'
         and rownum <= 10000  -- Reduce resultset
 ) 
GROUP BY 
   quartile
ORDER BY 
   1;

Results:

QUARTILE  StartingID  EndingID  CountPerGroup
    1        59261      93271       1000
    2        93281     110492       1000
    3       110512     122382       1000
    4       122391     134062       1000
    5       134072     144371       1000
    6       144372     154481       1000
    7       154482     162741       1000
    8       162752     169163       1000
    9       169173     176913       1000
   10       176921     183833       1000

PL/SQL: Joinrows - Convert multiple [returned cursor] rows into one row

This routine converts multiple rows into a single row. The trick is to pass the data via a SYS_REFCURSOR (see the CURSOR call in the example). Example Usage :

select joinrows(cursor(select contract_id from contract where rownum < 10 )) from dual;

CREATE OR REPLACE FUNCTION joinrows(
 sysref_cursor_in      sys_refcursor,
 s_delimiter_in        VARCHAR2 := ',' )
RETURN VARCHAR2
IS
   s_value            VARCHAR2(32767) := Null;
   s_result           VARCHAR2(32767) := Null;
   s_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN

   LOOP
      FETCH sysref_cursor_in
         INTO s_value;

      EXIT WHEN sysref_cursor_in%NOTFOUND;

      IF (s_result IS NOT NULL) THEN
       s_result := s_result||s_delimiter;
      END IF;

      s_result := s_result||s_value;

   END LOOP;

   RETURN s_result;

END joinrows;

Results:

0,2,3,9,37,404,818,991,1178

Oracle: SQL - Converting one row into two rows [multiple rows]

Problem : A single row needs to be converted into two rows.

Solution 1 :

Prerequite :

CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000)

Function : This routine handles an unlimited number of desired rows as opposed to Solution 2.

CREATE OR REPLACE FUNCTION split (
 s_delimited_list_in     VARCHAR2,
 s_delimiter_in          VARCHAR2 := ',')
RETURN prod_types.type_string_array PIPELINED
IS
/*
@Usage Example:
select * from table(split('one,two,three'));
*/
 l_idx               PLS_INTEGER;
 l_list              VARCHAR2(32767) := s_delimited_list_in;
 l_value             VARCHAR2(32767);
 ls_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN
LOOP
 l_idx := INSTR(l_list,ls_delimiter);

 IF (l_idx > 0) THEN

    PIPE ROW(SUBSTR(l_list,1,l_idx-1));
    l_list := SUBSTR(l_list,l_idx+LENGTH(ls_delimiter));
  
 ELSE

    PIPE ROW(l_list);
    EXIT;
  
 END IF;
END LOOP;

RETURN;

END SPLIT;

Sorry, there was a typo in the above script. It was fixed on 2/9/2009. Jason

The reverse of "split" is "joinrows".

Solution 2 :

Here is a simple example of converting one row into multiple rows. "atomic_contract" is returning a single row, but we need two columns from it to be returned as two rows.

select *
from (
select ac.*,
decode(t.row_count,1,ac.sk_seller,
                  2,ac.sk_buyer, null) sk_customer
from atomic_contract ac,
( select 1 row_count from dual
union all
select 2 row_count from dual) t
where contract_id = 3562777)