title: Get previous column value if the current column is null in a pivot
body: <p>I have a data like this:</p>
  
  <pre><code>Count | Status | Date
  1     | S1     | Jan 2020
  5     | S1     | Feb 2020
  10    | S1     | Mar 2020
  15    | S1     | Apr 2020
  1     | S2     | Jan 2020
                            &lt;- feb 2020 is missing for status2
  10    | S2     | Mar 2020
                            &lt;- apr 2020 is missing for status2
  
  </code></pre>
  
  <p>It is basically a cumulative data for each status per month and I needed the dates as column names then group them by status so I need to pivot it.</p>
  
  <p>When I pivot the given data it would look like this:</p>
  
  <pre><code>Status | Jan 2020 | Feb 2020 | Mar 2020 | Apr 2020
  S1     | 1        | 5        | 10       | 15
  S2     | 1        | NULL     | 10       | NULL
  </code></pre>
  
  <p>So what I want is when the column is NULL then it would get the previous column's data, so in this example the data for <code>Feb 2020</code> for Status <code>S2</code> should be <code>1</code> since it is the previous column that has value, also with the <code>Apr 2020</code> it should be <code>10</code> since it's the previous column with value.</p>
  
  <p>Something like this:</p>
  
  <pre><code>Status | Jan 2020 | Feb 2020 | Mar 2020 | Apr 2020
  S1     | 1        | 5        | 10       | 15
  S2     | 1        | 1 &lt;-     | 10       | 10 &lt;-
  </code></pre>
  
  <p>How can I achieve this one when you pivot a data like this?</p>
  
  <p>Here's a quick sample of what I have so far:</p>
  
  <pre><code>--Set the end of the month when getting the @columns variable
  DECLARE @toDate DATETIME = DATEADD(MONTH, 1, GETDATE()); 
  
  
  -- I have a function that gets the 1st month of the year until the month set by @toDate variable to set them as columns in the pivot so it would look like this:
  DECLARE @columns NVARCHAR(MAX)= '[Jan 2020],[Feb 2020],[Mar 2020],[Apr 2020]';
  
  
  SELECT * FROM   
  (
      SELECT DISTINCT 
          FORMAT(Date, 'MMM yyyy') AS [Date],
          SUM(COUNT(*)) OVER (PARTITION BY [Status] ORDER BY [Date]) AS [Count],
          [Status]
      FROM Table1
      WHERE Date &lt;= @toDate
      GROUP BY [Date], FORMAT([Date], 'MMM yyyy'), [Status]
  ) t 
  PIVOT(
      MAX(Count)
      for [Date] IN (@columns)
  ) AS pivot_table;   
  
  </code></pre>