PIVOT rotates an expression by turning the unique values from one vertical into multiple verticals and performs aggregations where they are required on any remaining column values that are needed in the output.
UNPIVOT performs the opposite operation to PIVOT by rotating verticals of an expression into their values. In another word, UNPIVOT flattens the records.
In this article, we will look into how to perform Pivot and UnPivot in C# with an example.
Introduction
PIVOT rotates an expression by turning the unique values from one vertical into multiple verticals and performs aggregations where they are required on any remaining column values that are needed in the output.
UNPIVOT performs the opposite operation to PIVOT by rotating verticals of an expression into their values. In another word, UNPIVOT flattens the records.
In this article, we will look into how to perform Pivot and UnPivot in C# with an example.
Objective
Let's say we have the below data set
The final output should look like
Straight to Experiment
Let us first create the below data structures
internal class InputData
{
public string ProductName { get; internal set; }
public int Year1 { get; internal set; }
public int Year2 { get; internal set; }
public int Year3 { get; internal set; }
public int Year4 { get; internal set; }
}
This data structure will hold the input data.
internal class OutputData
{
public string YearNo { get; internal set; }
public int Year { get; internal set; }
public int Sugarcane { get; internal set; }
public int Wheat { get; internal set; }
public int Rice { get; internal set; }
public int Dal { get; internal set; }
}
This data structure will hold the resultant data.
Now let us create a method say GetSourceRecords() that will hold the source data as under.
private static List<InputData> GetSourceRecords()
{
return new List<InputData>() {
new InputData() { ProductName = "Sugarcane", Year1 = 100, Year2 = 400, Year3 = 200, Year4 = 700 },
new InputData() { ProductName = "Wheat", Year1 = 340, Year2 = 900, Year3 = 670, Year4 = 234 },
new InputData() { ProductName = "Rice", Year1 = 678, Year2 = 567, Year3 = 378, Year4 = 987},
new InputData() { ProductName = "Dal", Year1 = 777, Year2 = 888, Year3 = 444, Year4 = 555 }
};
}
Now let us create a Multi-key dictionary (Dictionary<string, List<int>>). This will be our UNPIVOT function. It's purpose is to flatten the records.
private static Dictionary<string, List<int>> Unpivot(int row)
{
var source = GetSourceRecords();
List<int> yearwiseProductValues = new List<int>();
var multiKeyDictionary = new Dictionary<string, List<int>>();
yearwiseProductValues.Add(source[row].Year1);
yearwiseProductValues.Add(source[row].Year2);
yearwiseProductValues.Add(source[row].Year3);
yearwiseProductValues.Add(source[row].Year4);
multiKeyDictionary.Add(source[row].ProductName, yearwiseProductValues);
return multiKeyDictionary;
}
And invoke the same as under
//unpivot records
var unpivotRecords = new
{
Sugarcane = Unpivot(0)
,
Wheat = Unpivot(1)
,
Rice = Unpivot(2)
,
Dal = Unpivot(3)
};
The output at this point of UNPIVOT should look like
Now let us write out PIVOT function as under
private static List<OutputData> Pivot(dynamic unpivotRecords)
{
int count = 4;
int startYear = 2016;
var pivotRecords = new List<OutputData>();
for (int i = 0; i < count; i++)
{
pivotRecords.Add(new OutputData
{
YearNo = "Year-" + (i + 1)
,
Year = startYear + i
,
Sugarcane = unpivotRecords.Sugarcane["Sugarcane"][i]
,
Wheat = unpivotRecords.Wheat["Wheat"][i]
,
Rice = unpivotRecords.Rice["Rice"][i]
,
Dal = unpivotRecords.Dal["Dal"][i]
});
}
return pivotRecords;
}
This is our PIVOT function. It accepts the raw UNPIVOT records, starts from year : 2016 and loops four times, building the record in the way it is supposed to. Notice the way we are reading from the Multi-key dictionary.
This function is invoked as under
//pivot records
var pivotRecords = Pivot(unpivotRecords);
And finally, we need to display the transformed record as under.
pivotRecords.ForEach
(
i =>
Console.WriteLine("Year# : {0} Year : {1} Sugarcane : {2} Wheat : {3} Rice : {4} Dal : {5}",
i.YearNo, i.Year, i.Sugarcane, i.Wheat, i.Rice, i.Dal)
);
And the final result is
The complete program is as under
using System;
using System.Collections.Generic;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//unpivot records
var unpivotRecords = new
{
Sugarcane = Unpivot(0)
,
Wheat = Unpivot(1)
,
Rice = Unpivot(2)
,
Dal = Unpivot(3)
};
//pivot records
var pivotRecords = Pivot(unpivotRecords);
//display the records
Console.BackgroundColor = ConsoleColor.Blue;
Console.WriteLine("\t\t\t\tProduction in Quintals\t\t\t\t");
Console.BackgroundColor = ConsoleColor.DarkRed;
pivotRecords.ForEach
(
i =>
Console.WriteLine("Year# : {0} Year : {1} Sugarcane : {2} Wheat : {3} Rice : {4} Dal : {5}",
i.YearNo, i.Year, i.Sugarcane, i.Wheat, i.Rice, i.Dal)
);
Console.ReadKey();
}
#region Methods
//Source Records
private static List<InputData> GetSourceRecords()
{
return new List<InputData>() {
new InputData() { ProductName = "Sugarcane", Year1 = 100, Year2 = 400, Year3 = 200, Year4 = 700 },
new InputData() { ProductName = "Wheat", Year1 = 340, Year2 = 900, Year3 = 670, Year4 = 234 },
new InputData() { ProductName = "Rice", Year1 = 678, Year2 = 567, Year3 = 378, Year4 = 987},
new InputData() { ProductName = "Dal", Year1 = 777, Year2 = 888, Year3 = 444, Year4 = 555 }
};
}
//Unpivot records
private static Dictionary<string, List<int>> Unpivot(int row)
{
var source = GetSourceRecords();
List<int> yearwiseProductValues = new List<int>();
var multiKeyDictionary = new Dictionary<string, List<int>>();
yearwiseProductValues.Add(source[row].Year1);
yearwiseProductValues.Add(source[row].Year2);
yearwiseProductValues.Add(source[row].Year3);
yearwiseProductValues.Add(source[row].Year4);
multiKeyDictionary.Add(source[row].ProductName, yearwiseProductValues);
return multiKeyDictionary;
}
//Pivot records
private static List<OutputData> Pivot(dynamic unpivotRecords)
{
int count = 4;
int startYear = 2016;
var pivotRecords = new List<OutputData>();
for (int i = 0; i < count; i++)
{
pivotRecords.Add(new OutputData
{
YearNo = "Year-" + (i + 1)
,
Year = startYear + i
,
Sugarcane = unpivotRecords.Sugarcane["Sugarcane"][i]
,
Wheat = unpivotRecords.Wheat["Wheat"][i]
,
Rice = unpivotRecords.Rice["Rice"][i]
,
Dal = unpivotRecords.Dal["Dal"][i]
});
}
return pivotRecords;
}
#endregion
}
#region Data Structures
internal class OutputData
{
public string YearNo { get; internal set; }
public int Year { get; internal set; }
public int Sugarcane { get; internal set; }
public int Wheat { get; internal set; }
public int Rice { get; internal set; }
public int Dal { get; internal set; }
}
internal class InputData
{
public string ProductName { get; internal set; }
public int Year1 { get; internal set; }
public int Year2 { get; internal set; }
public int Year3 { get; internal set; }
public int Year4 { get; internal set; }
}
#endregion
}
Conclusion
In this article we have learnt -
- How to perform PIVOT and UNPIVOT in C#
- Creation of Multi-key dictionary
- Read and Write in a Multi-key dictionary
Hope this will be useful. Thanks for reading. Zipped file attached.