title: SQL - Display Months as columns
body: <p>I have a SQL Server table that has <code>Start</code> (1-1-2017) and <code>End</code> (1-1-2022) dates for contracts with invoices being generated each month for current and past months.</p>
  
  <p>I would like to display months as columns even when no invoice has been generated, is that possible with just SQL / Pivot tables or a table with dates as calendar must be created?</p>
  
  <p>I have worked with this code so far.</p>
  
  <pre><code>WITH CTE_MyTable AS
  (
       SELECT
           FORMAT(MIN(StartDate), 'yyyy-MM') AS [MyDate] 
       FROM
           MyTable 
  
       UNION ALL
  
       SELECT 
           FORMAT(MIN(DATEADD(month, 1, StartDate)), 'yyyy-MM') AS [MyDate] 
       FROM
           MyTable
       WHERE 
           FORMAT(DATEADD(month, 1, StartDate),'yyyy-MM') &lt;= (SELECT FORMAT(MAX(EndDate), 'yyyy-MM') AS [MyDate] FROM MyTable)
  )
  SELECT [MyDate] 
  FROM CTE_ MyTable 
  GROUP BY MyDate
  OPTION (MAXRECURSION 0);
  </code></pre>