SQL combine results

[dakota97]

Hi Al,,

 

I have a query that returns a set of results with 7 records as seen below. What I'm looking to do is to combine those 7 records into one record. The only differences are the values in "Value X" field.

 

http://cgdesign.net/images/sql.jpg

 

Thanks in advance,

 

-Chris

[joe_pool_is]

To do this, create a new DataTable using the format of the DataTables your SQL query returned.

           DataTable data = new DataTable("CustomTable");
           DataColumn dc1 = data.Columns.Add("User", typeof(int));
           DataColumn dc2 = data.Columns.Add("Date", typeof(DateTime));
           DataColumn dc3 = data.Columns.Add("Value1", typeof(string));
           DataColumn dc4 = data.Columns.Add("Value2", typeof(string));
           DataColumn dc5 = data.Columns.Add("Value3", typeof(string));

Then, loop through the data your query returned. If you want an item inserted, do that in your code to the new DataTable.

 

            foreach (DataRow row in sqlDataTable.Rows) {
             object objID = row["User"];
             if ((objID != null) && (objID != DBNull.Value) {
               int ID = (int)objID;
               string val1 = row["Value1"].ToString();
               if (!String.IsNullOrEmpty(val1)) {
                 // Add To Table (if not already there)
                 DataRow dr = null;
                 for (int i = 0; (i < data.Rows.Count) && (dr == null); i++) {
                   if (data.Rows[i][dc1].ToString().Equals(val1)) {
                     dr = data.Rows[i];
                   }
                 }
                 if (dr == null) {
                   dr = data.NewRow();
                   dr[dc1] = ID;
                   dr[dc2] = val1;
                   data.Rows.Add(dr);
                 }
               }
               // Continue on with your other rows
             }
           }

 

Finally, display the DataTable in your DataGridView (or whatever you are using):

           DataGridView1.DataSource = data.DefaultView;