How to group by a pivot table with LINQ

Question

I'm new to LINQ but from what I've read and looked up what I'm looking for is something like GroupBy - Simple 3. 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:

Query: (I'm working on Visual Studio 2012, querying an MS Access2010 Database)

        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;
        }

Trying to explain myself better, I want to turn something like this:

Query result Grouped by Department:

---

| ID | Description| Model | Department-- |

| 1 | Item1 -------| Model1 | Department1|

| 3 | Item3 -------| Model1 | Department1|

| 2 | Item2 -------| Model1 | Department2|

| 4 | Item4 -------| Model1 | Department2|

Into something like this:

Department 1

| ID | Description| Model |

| 1 | Item1 -------| Model1 |

| 3 | Item3 -------| Model1 |

Department 2

| ID | Description| Model |

| 2 | Item2 -------| Model1 |

| 4 | Item4 -------| Model1 |

Answer 1

I'm not 100% sure I follow, but here's my attempt at an answer. Here I build some input data (this mimics what you get back from the query) in a DataTable, and transform the data into a new DataSet using some LINQ. Does this answer your question?

// create input data
var inputData = new DataTable();
inputData.Columns.Add("ID", typeof (int));
inputData.Columns.Add("Description", typeof (string));
inputData.Columns.Add("Model", typeof (string));
inputData.Columns.Add("Department", typeof (string));
inputData.Rows.Add(1, "Item1", "Model1", "Department1");
inputData.Rows.Add(2, "Item2", "Model1", "Department2");
inputData.Rows.Add(3, "Item3", "Model1", "Department1");
inputData.Rows.Add(4, "Item4", "Model1", "Department2");
inputData.AcceptChanges();

// create output data
var outputData = new DataSet();
outputData.Tables.AddRange(
    inputData.AsEnumerable()
             .GroupBy(row => row.Field<string>("Department"))
             .Select(rowGroup => {
                                     var departmentTable = inputData.Clone();
                                     departmentTable.TableName = rowGroup.Key;
                                     foreach (var row in rowGroup)
                                         departmentTable.Rows.Add(row.ItemArray);
                                     return departmentTable;
                                 })
             .ToArray());
outputData.AcceptChanges();