Archive

Archive for May, 2011

Concatenate sets of intervals

Thursday, 5 May, 2011 Leave a comment
CLEAR;
with t as (
           select 11  AS ID, 1  AS val1, 'B'  AS val2 from dual union all
           select 12,1,'A' from dual union ALL
           select 13,1,'A' from dual union all           
           select 14,1,'B' from dual union ALL
           select 15,1,'A' from dual UNION ALL
           select 16,1,'B' from dual union ALL           
           select 17,1,'B' from dual union ALL
           select 18,1,'A' from dual union ALL           
           select 19,2,'B' from dual union all
           select 20,2,'A' from dual union ALL
           select 21,2,'B' from dual union ALL
           select 22,2,'A' from dual union ALL
           select 23,2,'B' from dual union ALL           
           select 24,2,'A' from dual union ALL           
           select 25,1,'B' from dual union ALL                      
           select 26,1,'A' from dual
           ),
tt AS(select t.*,
        case when lag (val2) over (partition by val1 order by id) || val2 in ('A', 'BA') then 1 end as start_of_group,
        case when lead(val2) over (partition by val1 order by id) || val2 in ('B', 'AB') then 1 end as   end_of_group
      from t),
ttt AS (SELECT tt.*, COUNT(start_of_group) over(PARTITION BY val1 ORDER BY id) AS group_no FROM tt),
tttt AS (SELECT row_number() over(ORDER BY val1, group_no) AS rn, 
       val1, 
       t1.ID AS id1, 
       t2.ID AS id2, 
       t1.val2 AS VAL2A, 
       t2.val2 AS VAL2B
  FROM (SELECT * FROM ttt WHERE start_of_group IS NOT NULL) t1
  FULL JOIN (SELECT * FROM ttt WHERE end_of_group IS NOT NULL) t2
 USING (val1, group_no)
 ORDER BY rn)
  SELECT * FROM tttt;
Advertisements
Categories: PL/SQL Tags: