analytics-In-list-processing.sql

REM Script: Analytics - In-list processing
REM SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script uses LAG to convert string list into a set of rows.

Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes

drop table t purge;

create table t as select '123,456,789' acct from dual;

select distinct (instr(acct||',',',',1,level)) loc
from t
connect by level <= length(acct)- length(replace(acct,','))+1;

select substr(acct,
 nvl(lag(loc) over ( order by loc),0)+1,
 loc-nvl(lag(loc) over ( order by loc),0)-1
 ) list_as_rows
from (
 select distinct (instr(acct||',',',',1,level)) loc
 from t
 connect by level <= length(acct)-length(replace(acct,','))+1
), t


;