title: sql how do i convert a time series data to a hoc
body: <p>I've a sql table which is of this format - </p>
  
  <pre><code>Company Size segment        DATE    
  ABC     Large cap           01-Mar-98    
  ABC     Large cap           01-Sep-98    
  ABC     Mid cap             01-Mar-99    
  ABC     Mid cap             01-Sep-99    
  ABC     Large cap           01-Mar-00    
  </code></pre>
  
  <p>i want to convert to this format - The end date is the last date of the each period. For example, the start date for 1st 'Large Cap' period is <code>01-Mar-98</code> and the end date for that period is <code>28-feb-99</code>.</p>
  
  <pre><code>Security    Size segment   Start date   End date       
  ABC        Large Cap       01-Mar-98    28-feb-99    
  ABC        Mid Cap         1-mar-99     28-feb-00    
  ABC        Large Cap       1-mar-00     NULL    
  </code></pre>
  
  <p>How do i do it in sql server?</p>
  
  <p>Thank you.</p>