public static DataTable SpeciesSitePivotTable(Guid speciesId, int year) { DataTable resultTable = new DataTable(); DataColumn locationName = new DataColumn("Species Name", typeof(string)); resultTable.Columns.Add(locationName); resultTable.PrimaryKey = new DataColumn[] { locationName }; resultTable.Columns.Add("Grand Total", typeof(int)); Dictionary <int, string> weekTranslation = new Dictionary <int, string>(); using (IDataReader reader = ResultsMapper.GetSpeciesBySite(speciesId, year)) { while (reader.Read()) { int weekNumber = reader.GetIntFromName("Week"); string week = string.Empty; if (weekTranslation.ContainsKey(weekNumber)) { week = weekTranslation[weekNumber]; } else { week = Conversion.GetDateForWeekNumber(weekNumber); } if (!resultTable.Columns.Contains(week)) { resultTable.Columns.Add(week, typeof(string)); } string speciesName = reader.GetValueFromName("CommonName").ToString(); int count = reader.GetIntFromName("SpeciesCount"); DataRow row = null; if (!resultTable.Rows.Contains(speciesName)) { row = resultTable.NewRow(); row["Species Name"] = speciesName; resultTable.Rows.Add(row); } else { row = resultTable.Rows.Find(speciesName); } row[week] = count.ToString(); int currentTotal = 0; if (!int.TryParse(row["Grand Total"].ToString(), out currentTotal)) { currentTotal = 0; } row["Grand Total"] = currentTotal + count; } } return(resultTable); }