Dataset Grouping

I have some data loaded into a dataset.  I would like to create a new dataset based on the grouping of that data.

ie. Right now I have every record in the dataset (one field is date).  I want to group by day and sum some of my totaling values, so my new dataset will have one record per day.  

Is there anyway to do this?
Fraser_AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COBOLdinosaurCommented:
Is there anyway to do this?You are going to have to give a lot more detail then that.  What kind a dataset? flat file? XML? relational database? mySQL? SQLserver? Oracle?  What cscripting language you use.  A sample of what the data looks like.

Cd&
0
jcrumbleCommented:
Also,  would it be possible to group the data prior to loading it into your dataset?  If your data is coming from an RDBMS, then it's simply a matter of changing your SQL.  What purpose are you trying to accomplish in grouping your data after it's in a dataset/datatable and how much data are you talking about?  Depending on how much data you are pulling it might be easier (and more logical) to simply pass a variable to your data extraction process(SQL?) to tell you how to group the data and simply reload the dataset.

Jeff
0
Fraser_AdminAuthor Commented:
No I cannot group prior.  I could do the temporary table thing and group, but I thought there might be a way to group it in a dataset.  I'm using vb.net accessing sqlserver 2000.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Fraser_AdminAuthor Commented:
The problem is, I need to do updates based on the initial dataset.  These updates need to be done in an order since the update of the current record is based on the values of the last record, which is not possible in sqlserver.  I cannot specify an order by in an update statement, and I cannot guarentee the order that will be returned.

These operations are for a report I want to generate.  They are done on a temporary table.

So basically I have a stored proc that gets all the records (3 different selections) and put them all into a temporary table.  I then return that table (ordered) from the stored proc.  I load the data into a vb.net datatable.  I then perform some updates on these records (based on the last record).  I then want to group the data before displaying it on the report.

The grouping can be done by day, month or year.  I would rather not traverse all the records to do this, but it looks like I might need to.  
0
Fraser_AdminAuthor Commented:
Another question, can I insert the contents of a datatable into an sqlserver table?
0
jcrumbleCommented:
You can definitely insert a datatable into SQL Server,

Here's some very simple sample syntax for reference (inline not a stored procedure but you should get the jist):

private void InsertInfo() {
      SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
      myConnection.Open();
      SqlTransaction myTrans = myConnection.BeginTransaction();
      SqlCommand myCommand = new SqlCommand();
      myCommand.Connection = myConnection;
      myCommand.Transaction = myTrans;
      try {
      for(int i=dataSet1.Tables["RetainConts"].Rows.Count-1; i>-1; i--){
        DataRow thisRow = dataSet1.Tables["RetainConts"].Rows[i];
            myCommand.CommandText = " INSERT INTO Projects( " +
            " ProjectName, Description, City, State, TypeID, ProjectDate, ProjectLength1, ProjectLength2, ProjectLengthU, StatusID, ArrivalDate, ModifyUser, ModifyDate"+
            " ) VALUES ("+
            " @ProjectName, @Description, @City, @State, @TypeID, @ProjectDate, @ProjectLength1, @ProjectLength2, @ProjectLengthU, @StatusID, @ArrivalDate, @ModifyUser, @ModifyDate"+
            " )";
            myCommand.Parameters.Add(new SqlParameter("@ProjectName", SqlDbType.NVarChar, 255));
            myCommand.Parameters["@ProjectName"].Value = ProjectName.Text;
.....
            myCommand.ExecuteNonQuery();
          myTrans.Commit();
            Message.Text = "<b>Base Information Added</b><br />";
            Session["dsData"] = "";
      } catch (SqlException f) {
          myTrans.Rollback();
            if (f.Number == 2627)
            Message.Text = "ERROR: A record already exists with the same primary key<br /><hr noshade='noshade' size='1' width='100%' />";
            else
            Message.Text = "ERROR: Could not add record, please ensure the fields are correctly filled out " + f.Number +"<br /><hr noshade='noshade' size='1' width='100%' />";
      } finally {
            myConnection.Close();
      }
}



0
jcrumbleCommented:
Also,

Although I haven't had the need to sort a DataTable, You might try updating a value in a DataTable and then adding that to SQL Server in the order of your temp field.  Seems like a loop inside a loop (with an if statement) would do.  Anyway here's somemore simple synax for updating a row in a datatable you might find useful:

      dataSet1.Tables["RetainConts"].Rows[SelRow]["FirmName"] = FirmName.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["Title"] = Title.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["WebSite"] = WebSite.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["Address1"] = Address1.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["Address2"] = Address2.Text.ToString();
etc....

I also just realized that my example above was in C#.  Don't know if you can translate it or not but hopefully you still get the idea.  

Good Luck,
Jeff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fraser_AdminAuthor Commented:
I get the idea, but I'm dealing with a temporary table, so after it is return from the sp, it no longer exists.
I'm looking to get out of traversing the whole table.  I thought briefly another table might help but it doesn't.  Basically what I need to do is further group this table and put it into another brand new table (sql server or vb.net datatable will do).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.