title: Create Intervals from start-end date and represent all the values - comma separated
body: <p>I have the following tables</p>
  
  <pre><code>CREATE TABLE PERSONS (
      PERSON_UID    NUMBER PRIMARY KEY,
      PERSON_NAME   VARCHAR2(100)
  );
  
  CREATE TABLE SKILLS (
      SKILL_UID    NUMBER PRIMARY KEY,
      SKILL_NAME   VARCHAR2(100)
  );
  
  CREATE TABLE PERSON_SKILLS (
      PERSON_SKILLS_UID   NUMBER,
      PERSON_FK           NUMBER,
      SKILL_FK            NUMBER,
      VALID_START         DATE,
      VAID_END            DATE
  );
  </code></pre>
  
  <p>Table data:</p>
  
  <h2>PERSONS table data</h2>
  
  <blockquote>
  <pre>
  PERSON_UID | PERSON_NAME
  ---------: | :----------
           1 | P1         
           2 | P2         
           3 | P3         
  </pre>
  </blockquote>
  
  <h2>SKILLS table data</h2>
  
  <blockquote>
  <pre>
  SKILL_UID | SKILL_NAME
  --------: | :---------
          1 | SKILL1    
          2 | SKILL2    
          3 | SKILL3    
          4 | SKILL4    
          5 | SKILL5    
          6 | SKILL6    
          7 | SKILL7    
          8 | SKILL8    
          9 | SKILL9    
         10 | SKILL10   
  </pre>
  </blockquote>
  
  <h2>PERSON_SKILLS table data</h2>
  
  <blockquote>
  <pre>
  PERSON_SKILLS_UID | PERSON_FK | SKILL_FK | VALID_START | VAID_END   
  ----------------: | --------: | -------: | :---------- | :----------
                  1 |         1 |        1 | 01-JAN-1990 | <em>null</em>       
                  2 |         1 |        2 | 01-JAN-1990 | 25-SEP-2001
                  4 |         1 |        6 | 01-JAN-1990 | 01-JAN-2010
                  5 |         1 |        7 | 01-JAN-1990 | <em>null</em>       
                  3 |         1 |        3 | 01-JUL-1990 | <em>null</em>       
                  6 |         1 |        9 | 31-DEC-2018 | <em>null</em>       
                  7 |         2 |        2 | 01-JAN-1990 | <em>null</em>       
                  9 |         2 |        8 | 01-JAN-1990 | 01-JAN-2001
                  8 |         2 |        3 | 01-JAN-1995 | 20-OCT-1998
                 10 |         3 |        9 | 01-JAN-1990 | <em>null</em>       
                 11 |         3 |        4 | 01-JAN-1990 | <em>null</em>       
                 12 |         3 |        5 | 01-JAN-1991 | <em>null</em>       
                 13 |         3 |        7 | 01-JAN-2005 | <em>null</em>       
  </pre>
  </blockquote>
  
  <p>Table PERSON_SKILLS contains the individual skills of a person with a valid start and valid end date. (NULL valid end date represents the SKILL is currently active)</p>
  
  <p>I want to create the date intervals using start/end dates and all the skills (Comma-separated) pertaining to that interval of the employee.</p>
  
  <p>Let's take the example of person 2: (I need output for all the employees in a single query)</p>
  
  <h2>Expected output</h2>
  
  <blockquote>
  <pre>
  PERSON_NAME | VALID_START | VALID_END   | SKILLS_OF_EMP         
  :---------- | :---------- | :---------- | :---------------------
  P2          | 01-JAN-1990 | 31-DEC-1994 | SKILL2, SKILL8        
  P2          | 01-JAN-1995 | 20-OCT-1998 | SKILL2, SKILL3, SKILL8
  P2          | 21-OCT-1998 | 01-JAN-2001 | SKILL2, SKILL8        
  P2          | 02-JAN-2001 | 31-DEC-4712 | SKILL2                
  </pre>
  </blockquote>
  
  <p>I have created the <a href="https://dbfiddle.uk/?rdbms=oracle_11.2&amp;fiddle=a894930987882232409bf7254459c7d3" rel="nofollow noreferrer">db&lt;>fiddle</a> with all table DDL, data and also expected output.</p>
  
  <p>Hoping to find the query which performs faster as I have around 18000 persons with average 15-16 Skills per person.</p>
  
  <p>Note: 31-Dec-4712 is the end of time.</p>