title: Create a new table by grouping the data in a table
body: <p>I have students and they eat morning and dinner. I want to have the name of every student's meal in the morning and evening.</p>
  
  <p>The tables I have; Student, Food and StudentFood table.</p>
  
  <pre><code>Table Of:StudentFood 
  
  StudentId FoodId FoodType FoodName
      3       1       1        a
      3       2       2        b
      4       3       1        c
      4       1       2        a
      5       4       1        d
      5       1       2        a
      6       1       1        a
      6       2       2        b
  </code></pre>
  
  <p><strong>Sample out, requested table;</strong></p>
  
  <pre><code>StudentId  MorningFood MorningFoodId EveningFood EveningFoodId
      3           a            1           b           2
      4           c            3           a           1
      5           d            4           a           1
      6           a            1           b           2
  </code></pre>
  
  <p>I was try;</p>
  
  <pre><code>SELECT
  StudentId,
  MAX(CASE WHEN FoodType=1 THEN FoodName END) AS MorningFood,
  MAX(CASE WHEN FoodType=1 THEN FoodId END) AS MorningFoodId,
  MAX(CASE WHEN FoodType=2 THEN FoodName END) AS EveningFood,
  MAX(CASE WHEN FoodType=2 THEN FoodId END) AS EveningFoodId
  FROM
  StudentFood
  GROUP BY StudentId,FoodType,FoodId
  </code></pre>
  
  <p>and result; :(</p>
  
  <pre><code>StudentId  MorningFood MorningFoodId EveningFood EveningFoodId
      3           a            1           null        null
      3           null         null        b           2
      4           c            3           null        null
      4           null         null        a           1
      5           d            4           null        null
      5           null         null        a           1
      6           a            1           null        null
      6           null         null        b           2
  </code></pre>