Home > PL/SQL > Concatenate sets of intervals

Concatenate sets of intervals

Thursday, 5 May, 2011 Leave a comment Go to comments

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:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: