title: SQL Server - Show All possible Values and Count Them
body: <p>I have the following table:</p>
  
  <pre><code>IdSce Year NoIte Value
  1     0    1     1 
  1     0    2     5
  1     0    3     1
  1     1    1     2 
  1     1    2     3
  1     1    3     2
  2     0    1     4     
  2     0    2     4
  2     0    3     1
  2     1    1     2 
  2     1    2     4
  2     1    3     3
  </code></pre>
  
  <p>I want to group by IdSce and Year, and show each possible value and count how many time each value appears like this:</p>
  
  <pre><code>IdSce Year Value1  Value2  Value3  Value4  Value5
  1     0    2       0       0       0       1 
  1     1    0       2       1       0       0
  2     0    1       0       0       2       0
  2     1    0       1       1       1       0
  </code></pre>
  
  <p>Thanks !</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>shawnt00 is really close to what I want, but I'm looking to do it as dynamic as possible, meaning if I have 10 different values for the column <em>value</em>, I will be missing information in my table. Therefore, if I have 10 different values, I want 10 new columns (value1, value2, ... , value10)</p>
  
  <p>This is what I've tried so far:</p>
  
  <pre><code>SELECT  IdSce 
          ,Year
          ,SUM(CASE WHEN Value &gt;= 0 and Value &lt; 1 THEN 1 else 0 end) Zero
          ,SUM(CASE WHEN Value &gt;= 1 and Value &lt; 2 THEN 1 else 0 end) One
          ,SUM(CASE WHEN Value &gt;= 2 and Value &lt; 3 THEN 1 else 0 end) Two   
          ,SUM(CASE WHEN Value &gt;= 3 and Value &lt; 4 THEN 1 else 0 end) Three
          ,SUM(CASE WHEN Value &gt;= 4 and Value &lt; 5 THEN 1 else 0 end) Four
          ,SUM(CASE WHEN Value &gt;= 5 THEN 1 else 0 end) FiveMore
          ,SUM(CASE WHEN Value &gt;= 0 THEN 1 else 0 end) Total  
  FROM    Table
  GROUP BY    IdSce
              ,Year
  </code></pre>
  
  <p>Thanks for the help again!</p>