Ado.net Century Problem for All Can you return 10 result set and show it in one table ? Answer : ??

you will need to use a DataTable.

here are your steps.

1.  get a list of the "Items" from your Table. Use a DataSet and DataTable:

DataSet ds = new DataSet();

using(SqlConnection cn = new SqlConnection("your connection string"))
{
     SqlCommand cmd = new SqlCommand();
     cmd.Connection = cn;
     cmd.CommandText = "sp_GetItems" //The name of your stored procedure
     cmd.CommandType = CommandType.StoredProcedure;

     // If you need parameters for your stored procedure
     //SqlParameterCollection sqlParams = cmd.Parameter;
     //sqlParams.AddWithValue("[name of parameter]", [data for parameter]);

     SqlDataAdapter myDA = new SqlDataAdapter(cmd);
     DataTable myDT = new DataTable("ITEMS");

     DataColumn myDC = myDT.Columns.Add("main_name", Type.GetType("System.String", true, true)); // Name of the column that is being retrieved from SP
    
     myDA.Fill(ds, "ITEMS");
}

2.  Now, create a DataTable to store the list of Items, and create columns for the yearly totals....ie:

DataTable myItemDT = ds.Tables["ITEMS"];

DataColumn myDC = new DataColumn();
myDC.ColumnName = "Year 2007";
myDC.DataType = Type.GetType("System.String", true, true);
myDT.Columns.Add(myDC);

myDC = null;

myDC = new DataColumn();
myDC.ColumnName = "Year 2006";
myDC.DataType = Type.GetType("System.String", true, true);
myDT.Columns.Add(myDC);

myDC = null;

myDC = new DataColumn();
myDC.ColumnName = "Year 2008";
myDC.DataType = Type.GetType("System.String", true, true);
myDT.Columns.Add(myDC);

myDC = null;

3.  Now, requery for the all the data that you want...

SQL Statement:  "SELECT Main_Name, Cost, dayDate FROM ENTRY" (use a stored procedure, I will name it "sp_GetEntries")

4.  Now, you will run the SP, then loop through the data, compare it to the first DataTable, and roll-up the totals....

DataRow myDR = new DataRow();

using(SqlConnection cn = new SqlConnection("your connection string"))
{
     SqlCommand cmd = new SqlCommand();
     cmd.Connection = cn;
     cmd.CommandText = "sp_GetEntires" //The name of your stored procedure
     cmd.CommandType = CommandType.StoredProcedure;

     cn.Open();
     SqlDataReader myEntries = cmd.ExecuteReader();
  
     while(myEntries.Read())
     {
          myDR = myNewDT.Rows.Find(myEntries["Main_Name"];
  
          if (myDR != null)
          {
               myDR.BeginEdit();

               if (Convert.ToDateTime(myEntries["dayDate"]).Year == 2007)
               {
                     if (myDR["Year 2007"] is System.Null)
                     {
                            // If no data has been written to this cell in the DataTable --- blank
                            myDR["Year 2007"] = myEntries["Cost"].ToString();
                     }
                     else
                     {
                           // If data has been written to this cell in the DataTable, we want to add the current Cost to the already accumilated cost
                          myDR["Year 2007"] = Convert.ToDecimal(myDR["Year 2007"]) + Convert.ToDecimal(myEntries["Cost"]);
                     }

               if (Convert.ToDateTime(myEntries["dayDate"]).Year == 2006)
               {
                     if (myDR["Year 2006"] is System.Null)
                     {
                            // If no data has been written to this cell in the DataTable --- blank
                            myDR["Year 2006"] = myEntries["Cost"].ToString();
                     }
                     else
                     {
                           // If data has been written to this cell in the DataTable, we want to add the current Cost to the already accumilated cost
                          myDR["Year 2006"] = Convert.ToDecimal(myDR["Year 2006"]) + Convert.ToDecimal(myEntries["Cost"]);
                     }

               if (Convert.ToDateTime(myEntries["dayDate"]).Year == 2008)
               {
                     if (myDR["Year 2008"] is System.Null)
                     {
                            // If no data has been written to this cell in the DataTable --- blank
                            myDR["Year 2008"] = myEntries["Cost"].ToString();
                     }
                     else
                     {
                           // If data has been written to this cell in the DataTable, we want to add the current Cost to the already accumilated cost
                          myDR["Year 2008"] = Convert.ToDecimal(myDR["Year 2008"]) + Convert.ToDecimal(myEntries["Cost"]);
                     }
               }
  
               myDR.EndEdit();
          }
     }
  
     myEntries.Close();
}

dataGridView1.DataSource = myDT.DefaultView;


that should do it for you.  If you want a $ with the cost, then you can do a String.Format for currency.