title: Oracle timeline report from overlapping intervals
body: <p>I am using Oracle 10g, and I have a table with overlapping intervals. In a simplified form:</p>
  
  <pre><code>create TABLE INTERVAL_TEST (
      STARTDATE DATE NOT NULL,
      ENDDATE DATE,
      ITEM VARCHAR2(100) NOT NULL
  )
  
  insert into INTERVAL_TEST (STARTDATE, ENDDATE, ITEM) values (to_date('2012-01-01', 'YYYY-MM-DD'), null, 'AAA');
  insert into INTERVAL_TEST (STARTDATE, ENDDATE, ITEM) values (to_date('2012-02-01', 'YYYY-MM-DD'), to_date('2012-03-01', 'YYYY-MM-DD'), 'BBB');
  </code></pre>
  
  <p>As you see, there are overlapping entries. The null in the first one means that this is valid "forever".
  I need to generate a report that shows the history on a timeline, listing the number of valid items for each line. There should be no time-overlap in the output, instead all the valid items should be listed for each subinterval where there was no change:</p>
  
  <pre><code>START       END          VALID_ITEMS
  ----------  ----------   --------------
  2012-01-01  2012-02-01   AAA
  2012-02-01  2012-03-01   AAA, BBB
  2012-03-01  NULL         AAA
  </code></pre>
  
  <p>Is there a SQL statement that generates such a result?    </p>