How to set rows in a DataTable with the same column value?

Question

For instance, given the table:

MSDS ID	Gallons	Color Description
8001	50	Blue1
8001	15	Blue2
8001	30	Blue3
8002	12	Blue4
8002	10	Blue5
8003	25	Blue6

I would like to group all the rows by the MSDS ID value and set the column "Gallons" to match what's in the first row for each group.

The result should look like this:

MSDS ID	Gallons	Color Description
8001	50	Blue1
8001	50	Blue2
8001	50	Blue3
8002	12	Blue4
8002	12	Blue5
8003	25	Blue6

I'm now sure if I can do this in SQL. Here is how I'm populating it from SQL:

select   
  b.booth_id, b.pnt_msds msds_id, bd.colorDescription, 0.00 as Total_MSDS_Gallons_Used   
from table1  b   
join table2 bd  on bd.booth_id = b.booth_id  
where b.entry_date BETWEEN cast(@startDate as date)
  AND cast(@endDate as date) AND cast(@endDate as date) 
order by msds_id, Total_MSDS_Gallons_Used desc 

Then I go through and populate the row Gallons programmatically as such (results from table for the Gallons column don't reflect this exactly as that's just for visualization purposes).

   private DataTable UpdateGallonColumns(DataTable dt)
        {

            // sets the values for the first row
            string sbooth = dt.Rows[0]["booth_id"].ToString();
            string smsds = dt.Rows[0]["msds_id"].ToString();
            
            decimal numofgallons = GetNumberOfGallons(dt.Rows[0]); 
            decimal gallonsPerMSDS = numofgallons; 
            dt.Rows[0]["Total_MSDS_Gallons_Used"] = numofgallons; 

            // foreach row in the datatable after the first row 
            foreach (DataRow row in dt.Rows.Cast<DataRow>().Skip(1))
            {
                numofgallons = GetNumberOfGallons(row);
                if (sbooth != row["booth_id"].ToString())
                {
                    sbooth = row["booth_id"].ToString();
                    gallonsPerMSDS = numofgallons;
                }
                else
                {
                    if (smsds != row["msds_id"].ToString())
                    {
                        smsds = row["msds_id"].ToString();
                        gallonsPerMSDS = numofgallons;
                    }
                    else
                        gallonsPerMSDS += numofgallons;
                }
                row["Total_MSDS_Gallons_Used"] = gallonsPerMSDS;
           

            }
            return dt;
        }

Answer 1

You can copy the source-table, use Linq to group by the ID and then modify each row:

DataTable resultTable = sourceTable.Copy();
var idGroups = resultTable.AsEnumerable().GroupBy(r => r.Field<int>("MSDS ID"));

foreach (var idGroup in idGroups)
{
    int gallons = idGroup.First().Field<int>("Gallons");
    foreach (DataRow row in idGroup)
        row.SetField("Gallon", gallons);
}