title: Filling up dummy columns in pivot result sql server
body: <p>I have an attendance table with the following columns:</p>
  
  <pre><code>StudentID, Date, Attendance
  </code></pre>
  
  <p>Now I'm running a query to get attendance results</p>
  
  <pre><code>DECLARE @paramList VARCHAR(MAX)
  
  
  SET @paramList = STUFF((SELECT DISTINCT ',[' + 
  CONVERT(varchar(10), [Date], 20) + ']' 
  FROM AttendenceT  
  WHERE Date &gt; '2014-01-01' AND Date &lt; '2014-01-31' 
  FOR XML PATH('') ) ,1,1,'')
  
  DECLARE @query NVARCHAR(MAX)
  
  SET @query = 'SELECT StudentID, ' + @paramList
  + ' FROM( SELECT * FROM AttendenceT)src 
  PIVOT(SUM(Attendence) FOR Date IN (' + @paramList + ')) pvt'
  
  EXEC sp_executesql @query
  </code></pre>
  
  <p>The results are</p>
  
  <p><img src="https://i.stack.imgur.com/EDQHS.png" alt="enter image description here"></p>
  
  <p>The result is ok except that for missing days in attendance table, no column is available in the result. I want to modify the query so that even if date is not present in attendance table, its column is still available. </p>
  
  <p>Please note that I'm not an expert at SQL. I've created this query using google and mostly stackoverflow answers of past questions.</p>