title: How to group by a pivot table with LINQ
body: <p>I'm new to LINQ but from what I've read and looked up what I'm looking for is something like <a href="http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b" rel="nofollow noreferrer">GroupBy - Simple 3</a>. Issue is I don't want to group by a little object, I want to group by and separate by those groups a pivot table (or directly the query that makes it) that looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/iCxSd.png" alt="Pivot Table"></p>
  
  <p>Query:
  (I'm working on Visual Studio 2012, querying an MS Access2010 Database)</p>
  
  <pre><code>        try
          {
              l.Open();
              OleDbCommand cmd = l.CreateCommand();
              string transform = "TRANSFORM Max(Ultima_Calibracao) AS MaxUltimaCalib ";
              string select = "SELECT Codigo, B_DSC AS Artigo, Marca_ AS Marca, Modelo, N_Serie ";
              string from = "FROM APP_Equip_Ult_Prox_Calibracao ";
              string where = "WHERE YEAR(Ultima_Calibracao) = " + ano +
                  " AND [Descrição Serviço] like '" + servico + "'   ";
              string groupby = "GROUP BY APP_Equip_Ult_Prox_Calibracao.Codigo, APP_Equip_Ult_Prox_Calibracao.B_DSC, " +
                  "APP_Equip_Ult_Prox_Calibracao.Marca_, APP_Equip_Ult_Prox_Calibracao.Modelo, APP_Equip_Ult_Prox_Calibracao.N_Serie," +
                  "Year(DateAdd('m',Val([frequência calibração]),[Ultima_Calibracao])) ";
              string pivot = "PIVOT Format(Ultima_Calibracao,'yyyy-mm') IN ( '" + ano + "-01','" + ano + "-02','" + ano + "-03','" + ano + "-04','" + ano + "-05','" +
                  ano + "-06','" + ano + "-07','" + ano + "-08','" + ano + "-09','" + ano + "-10','" + ano + "-11','" + ano + "-12');";
              string query = transform + select + from + where + groupby + pivot;
  
              cmd = new OleDbCommand(query, l);
  
              dt = new DataTable();
              dt.Load(cmd.ExecuteReader());
              l.Close();
  
          }
          catch (Exception ex)
          {
              System.Diagnostics.Debug.WriteLine("DAO Exception: " + ex.Message);
              return null;
          }
  </code></pre>
  
  <p>Trying to explain myself better, I want to turn something like this:</p>
  
  <p>Query result Grouped by Department:</p>
  
  <hr>
  
  <h2>| ID | Description| Model  | Department-- |</h2>
  
  <h2>| 1  | Item1 -------| Model1 | Department1|</h2>
  
  <h2>| 3  | Item3 -------| Model1 | Department1|</h2>
  
  <h2>| 2  | Item2 -------| Model1 | Department2|</h2>
  
  <h2>| 4  | Item4 -------| Model1 | Department2|</h2>
  
  <p>Into something like this:</p>
  
  <p>Department 1</p>
  
  <h2>| ID | Description| Model  |</h2>
  
  <h2>| 1  | Item1 -------| Model1 |</h2>
  
  <h2>| 3  | Item3 -------| Model1 |</h2>
  
  <p>Department 2</p>
  
  <h2>| ID | Description| Model  |</h2>
  
  <h2>| 2  | Item2 -------| Model1 |</h2>
  
  <h2>| 4  | Item4 -------| Model1 |</h2>