title: SQL Server 2008 grouping based on 2 columns which contains null values
body: <p>My question is in the table structure below. All items grouped by <code>storeName</code> and <code>storeId</code> then displays the total of each item for each store. But some of the items not sold in some stores therefore displays NULL.. Is that possible to</p>
  
  <ul>
  <li>display <strong>egg&amp;bacon</strong> instead of <code>NULL</code> for the first 8 records in <code>itemName</code> column and </li>
  <li>display <strong>coke</strong> for instead of <code>NULL</code> the second 8  in <code>itemName</code> column and so on.</li>
  </ul>
  
  <p>I do not know the how many item they have. So it is dynamic.</p>
  
  <p>I want to do this because I have created a crosstab report with pentaho report designer. It works fine but it expects data in order to display it properly. So each item must have 8 records, one for each store. So I can group it by store and <code>itemname</code>. So the report displays - or 0 for the items not sold for those stores.   </p>
  
  <p>what I have </p>
  
  <pre><code>storeId  storeName itemName  total
  1        storeA    egg&amp;bacon 75
  2        storeB    NULL      NULL
  3        storeC    egg&amp;bacon 30
  4        storeD    NULL      NULL
  5        storeE    NULL      NULL
  6        storeF    egg&amp;bacon 50
  7        storeG    NULL      NULL
  8        storeH    NULL      NULL
  1        storeA    coke      105
  2        storeB    coke      90
  3        storeC    coke      60
  4        storeD    NULL      NULL
  5        storeE    coke      20
  6        storeF    coke      80
  7        storeG    NULL      NULL
  8        storeH    NULL      NULL
  </code></pre>
  
  <p>what I want (If the table below is not realistic, I can create a separate column to group them but again how?)</p>
  
  <pre><code>    storeId  storeName itemName  total
      1        storeA    egg&amp;bacon 75
      2        storeB    egg&amp;bacon 0
      3        storeC    egg&amp;bacon 30
      4        storeD    egg&amp;bacon 0
      5        storeE    egg&amp;bacon 0
      6        storeF    egg&amp;bacon 50
      7        storeG    egg&amp;bacon 0
      8        storeH    egg&amp;bacon 0
      1        storeA    coke      105
      2        storeB    coke      90
      3        storeC    coke      60
      4        storeD    NULL      0
      5        storeE    coke      20
      6        storeF    coke      80
      7        storeG    NULL      0
      8        storeH    NULL      0
  </code></pre>
  
  <p>This is the sample SQL statement</p>
  
  <pre><code>SELECT storeId , storeName, itemName, total FROM STORE
   LEFT OUTER JOIN
  (
      SELECT storeId, storeName, itemId,  total FROM REVENUE_CENTER as RVC
      LEFT OUTER JOIN MENU_ITEM_TOTAL as MIT ON STORE.storeId = MIT.storeId       
  ) as subQ ON STORE.storeId = subQ.storeId
  
  LEFT OUTER JOIN MENU_ITEM as MI ON  MI.itemId = subQ.itemId
  </code></pre>
  
  <p>thanks in advance
  Oz.</p>