Round #578

Pivot a table of monthly_sales (month, year, total) to columns per month.

```csharp using System; using System.Collections.Generic; using System.Linq; public class MonthlySales { public string Month { get; set; } public int Year { get; set; } public int Total { get; set; } } class Program { static void Main() { var monthlySales = new List<MonthlySales> { new MonthlySales { Month = "Jan", Year = 2022, Total = 100 }, new MonthlySales { Month = "Feb", Year = 2022, Total = 150 }, new MonthlySales { Month = "Mar", Year = 2022, Total = 120 }, new MonthlySales { Month = "Jan", Year = 2023, Total = 130 }, new MonthlySales { Month = "Feb", Year = 2023, Total = 160 }, new MonthlySales { Month = "Mar", Year = 2023, Total = 140 }, }; var pivotedSales = monthlySales.GroupBy(s => s.Year) .Select(g => new { Year = g.Key, Jan = g.Where(s => s.Month == "Jan").Select(s => s.Total).FirstOrDefault(), Feb = g.Where(s => s.Month == "Feb").Select(s => s.Total).FirstOrDefault(), Mar = g.Where(s => s.Month == "Mar").Select(s => s.Total).FirstOrDefault(), }); foreach (var sale in pivotedSales) { Console.WriteLine($"Year: {sale.Year}, Jan: {sale.Jan}, Feb: {sale.Feb}, Mar: {sale.Mar}"); } } } ```

```csharp using System; using System.Data; using System.Linq; public class SalesPivot { public static DataTable PivotMonthlySales(DataTable monthlySales) { // Check if the input DataTable is empty if (monthlySales.Rows.Count == 0) { throw new ArgumentException("Input DataTable is empty"); } // Get unique months and years var months = monthlySales.AsEnumerable().Select(r => r.Field<string>("month")).Distinct().OrderBy(m => m).ToList(); var years = monthlySales.AsEnumerable().Select(r => r.Field<int>("year")).Distinct().OrderBy(y => y).ToList(); // Create a new DataTable with pivoted columns var pivotedTable = new DataTable(); pivotedTable.Columns.Add("year", typeof(int)); // Add columns for each month foreach (var month in months) { pivotedTable.Columns.Add(month, typeof(int)); } // Populate the pivoted DataTable foreach (var year in years) { var newRow = pivotedTable.NewRow(); newRow["year"] = year; foreach (var month in months) { var salesRow = monthlySales.AsEnumerable() .FirstOrDefault(r => r.Field<string>("month") == month && r.Field<int>("year") == year); if (salesRow != null) { newRow[month] = salesRow.Field<int>("total"); } else { newRow[month] = DBNull.Value; } } pivotedTable.Rows.Add(newRow); } return pivotedTable; } public static void Main() { // Example usage var monthlySales = new DataTable(); monthlySales.Columns.Add("month", typeof(string)); monthlySales.Columns.Add("year", typeof(int)); monthlySales.Columns.Add("total", typeof(int)); monthlySales.Rows.Add("January", 2022, 100); monthlySales.Rows.Add("February", 2022, 120); monthlySales.Rows.Add("March", 2022, 150); monthlySales.Rows.Add("January", 2023, 110); monthlySales.Rows.Add("February", 2023, 130); monthlySales.Rows.Add("March", 2023, 160); var pivotedTable = PivotMonthlySales(monthlySales); // Print the pivoted DataTable foreach (DataColumn column in pivotedTable.Columns) { Console.Write(column.ColumnName + "\t"); } Console.WriteLine(); foreach (DataRow row in pivotedTable.Rows) { foreach (object value in row.ItemArray) { Console.Write(value + "\t"); } Console.WriteLine(); } } } ``` This C# code defines a method `PivotMonthlySales` that takes a `DataTable` containing monthly sales data and returns a new `DataTable` with the data pivoted to have columns for each month. The `Main` method demonstrates how to use this function with an example `DataTable`. The `PivotMonthlySales` method first checks if the input `DataTable` is empty and throws an exception if it is. It then retrieves the unique months and years from the input data. A new `DataTable` is created with columns for each month, and the data is populated by iterating over the unique years and months. The example usage in the `Main` method creates a sample `DataTable` with monthly sales data for two years, pivots the data using the `PivotMonthlySales` method, and prints the resulting pivoted `DataTable`.