Dataset Grouping

Posted on 2004-11-02
Last Modified: 2008-09-12
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?
Question by:Fraser_Admin
    LVL 53

    Expert Comment

    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.

    LVL 4

    Expert Comment

    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.


    Author Comment

    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 accessing sqlserver 2000.

    Author Comment

    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 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.  

    Author Comment

    Another question, can I insert the contents of a datatable into an sqlserver table?
    LVL 4

    Expert Comment

    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"]);
          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;
                Message.Text = "<b>Base Information Added</b><br />";
                Session["dsData"] = "";
          } catch (SqlException f) {
                if (f.Number == 2627)
                Message.Text = "ERROR: A record already exists with the same primary key<br /><hr noshade='noshade' size='1' width='100%' />";
                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 {

    LVL 4

    Accepted Solution


    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();

    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,

    Author Comment

    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 datatable will do).

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
    This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now