title: SQL: How can I display full 7 days of the week with my data that only represents some days of the week?
body: <p>I have a table that stores data on days of the week.  I'm wanting to return a row for each day of the week even if there isn't any rows for that day of the week in my table.  Here's my current sql select statement.</p>
  
  <pre><code>SELECT StoreID,
     CASE 
         WHEN S.[DayOfWeek] = 1 THEN 'Sunday' 
         WHEN S.[DayOfWeek] = 2 THEN 'Monday'
         WHEN S.[DayOfWeek] = 3 THEN 'Tuesday'
         WHEN S.[DayOfWeek] = 4 THEN 'Wednesday'
         WHEN S.[DayOfWeek] = 5 THEN 'Thursday'
         WHEN S.[DayOfWeek] = 6 THEN 'Friday'
         WHEN S.[DayOfWeek] = 7 THEN 'Saturday'
         ELSE 'BAD' 
     END AS [DayOfWeek], 
         isOpen
    FROM MyTable S
   WHERE StoreID = @I_StoreID
   ORDER BY S.[DayOfWeek]  
  </code></pre>
  
  <p>Right now it returns just a Monday  and Tuesday record because that's all that exists int he table, but I want it to also return the other rows even though there are no records currently for them. Thanks!</p>
  
  <p>Edit:</p>
  
  <p>Here is what I have ...</p>
  
  <pre><code>  StoreID | DayOfWeek | isOpen
  
        22     Sunday      0
        22     Monday      1
        29     Sunday      0
  </code></pre>
  
  <p>Here is what I'm hoping to get...</p>
  
  <pre><code>  StoreID | DayOfWeek | isOpen
  
        22     Sunday      0
        22     Monday      1
        22     Tuesday     NULL
         ....
        22     Saturday    NULL
  
        29     Sunday      1
        29     Monday      NULL 
        29     Tuesday     NULL
         ....
        29     Saturday    NULL
  </code></pre>