Oracle merge rows with multiple attributes changing in time on one row.

Oracle_merge_validity.sql

CREATE TABLE TST_MGA (
  from_time NUMBER(22) NOT NULL,
  to_time NUMBER(22) NOT NULL,
  mga NUMBER(22) NOT NULL);

CREATE TABLE tst_mga_mba(
  from_time NUMBER(22) NOT NULL,
  to_time NUMBER(22) NOT NULL,
  mga NUMBER(22) NOT NULL,
  mba NUMBER(22) NOT NULL);

CREATE TABLE tst_mga_dso(
  from_time NUMBER(22) NOT NULL,
  to_time NUMBER(22) NOT NULL,
  mga NUMBER(22) NOT NULL,
  dso NUMBER(22) NOT NULL);

/* validities (valid_from is exclusive):
MGA1 = 2010-2020
MGA1-MBA1 = 2010-2015
MGA1-MBA2 = 2015-2020
MGA1-DSO1 = 2010-2011
MGA1-DSO2 = 2011-2020*/

INSERT INTO TST_MGA VALUES (2010, 2020, 1);
INSERT INTO tst_mga_mba VALUES (2010, 2015, 1, 1);
INSERT INTO tst_mga_mba VALUES (2015, 2020, 1, 2); 
INSERT INTO tst_mga_dso VALUES (2010, 2011, 1, 1);
INSERT INTO tst_mga_dso VALUES (2011, 2020, 1, 2); 

/*result looks like:
2010-2011 MGA1 MBA1 DSO1
2011-2015 MGA1 MBA1 DSO2
2015-2020 MGA1 MBA2 DSO2
*/


WITH DATA AS
  (SELECT mga, from_time, to_time, NULL AS dso, mba
   FROM tst_mga_mba mb
   UNION
   SELECT mga, from_time, to_time, dso, NULL AS mba
   FROM tst_mga_dso d)
SELECT BREAKS.mga,
       BREAKS.from_time,
       BREAKS.to_time,
       Max(DATA.mba) AS mba,
       Max(DATA.dso) AS dso
FROM ( 
  --3. seznam vsech usecek grafu, zatim bez hodnot
  SELECT mga,
         from_time,
         to_time
  FROM (
      -- 2. k pocatkum intervalu doplnit i konce
	    SELECT mga,
	           from_time,
	           LEAD(from_time) OVER (PARTITION BY mga ORDER BY from_time) to_time
	    FROM ( 
 	    	-- 1. seznam vsech zmen hodnot (pocatky a konce existujicich intervalu)
            SELECT DISTINCT D.mga, DECODE(X.X, 1, D.from_time, D.to_time) from_time
	        FROM
	           (SELECT DATA.mga,
	                    DATA.from_time,
	                    DATA.to_time
	            FROM DATA ) D
	            CROSS JOIN (SELECT ROWNUM X FROM DUAL CONNECT BY LEVEL <= 2) X)
    -- 4. skrtnout posledni (zacinajici ld_EndT)
	) WHERE to_time IS NOT NULL) BREAKS
--5. ke kazde usecce doplnit hodnotu vykonu
-- pokud nemaji byt generovany meziuseky s hodnotou 0, tak inner join
JOIN DATA ON DATA.mga = BREAKS.mga
	AND DATA.from_time <= BREAKS.from_time
    AND BREAKS.from_time < DATA.to_time
GROUP BY BREAKS.mga,
         BREAKS.from_time,
         BREAKS.to_time
ORDER BY BREAKS.mga,
         BREAKS.from_time,
         BREAKS.to_time