How to pivot and unpivot using C#?

Rajnilari2015
Posted by Rajnilari2015 under C# category on | Points: 40 | Views : 682
Let's say we have the below RecordSet

Product Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
Product1 1 2 3 4 5 6 7 8 9 10
Product2 2 3 4 5 6 7 8 9 10 11
Product3 3 4 5 6 7 8 9 10 11 12
Product4 4 5 6 7 8 9 10 11 12 13


The processed output should look as under

YKey = Y1  Year = 2016 Product1 = 1 Product2 = 2 Product3 = 3 Product4 = 4
YKey = Y2 Year = 2017 Product1 = 2 Product2 = 3 Product3 = 4 Product4 = 5
YKey = Y3 Year = 2018 Product1 = 3 Product2 = 4 Product3 = 5 Product4 = 6
YKey = Y4 Year = 2019 Product1 = 4 Product2 = 5 Product3 = 6 Product4 = 7
YKey = Y5 Year = 2020 Product1 = 5 Product2 = 6 Product3 = 7 Product4 = 8
YKey = Y6 Year = 2021 Product1 = 6 Product2 = 7 Product3 = 8 Product4 = 9
YKey = Y7 Year = 2022 Product1 = 7 Product2 = 8 Product3 = 9 Product4 = 10
YKey = Y8 Year = 2023 Product1 = 8 Product2 = 9 Product3 = 10 Product4 = 11
YKey = Y9 Year = 2024 Product1 = 9 Product2 = 10 Product3 = 11 Product4 = 12
YKey = Y10 Year = 2025 Product1 = 10 Product2 = 11 Product3 = 12 Product4 = 13


The below program will helop us to do so

using System;
using System.Collections.Generic;

namespace ConsoleApplication3
{
class Program
{

static void Main(string[] args)
{
//unpivot records
var unpivotRecords = new
{
Product1 = Unpivot(0)
,
Product2 = Unpivot(1)
,
Product3 = Unpivot(2)
,
Product4 = Unpivot(3)
};

//pivot records
var pivotRecords = Pivot(unpivotRecords);

//display the records
pivotRecords.ForEach
(
i =>

Console.WriteLine("YKey = {0} Year = {1} Product1 = {2} Product2 = {3} Product3 = {4} Product4 = {5}",
i.YKey, i.Year, i.Product1, i.Product2, i.Product3, i.Product4)
);

Console.ReadKey();
}


#region Methods

//Source Records
private static List<InputData> GetSourceRecords()
{
List<InputData> source = new List<InputData>();
source.Add(new InputData() { ProductName = "Product1", Year1 = 1, Year2 = 2, Year3 = 3, Year4 = 4, Year5 = 5, Year6 = 6, Year7 = 7, Year8 = 8, Year9 = 9, Year10 = 10 });
source.Add(new InputData() { ProductName = "Product2", Year1 = 2, Year2 = 3, Year3 = 4, Year4 = 5, Year5 = 6, Year6 = 7, Year7 = 8, Year8 = 9, Year9 = 10, Year10 = 11 });
source.Add(new InputData() { ProductName = "Product3", Year1 = 3, Year2 = 4, Year3 = 5, Year4 = 6, Year5 = 7, Year6 = 8, Year7 = 9, Year8 = 10, Year9 = 11, Year10 = 12 });
source.Add(new InputData() { ProductName = "Product4", Year1 = 4, Year2 = 5, Year3 = 6, Year4 = 7, Year5 = 8, Year6 = 9, Year7 = 10, Year8 = 11, Year9 = 12, Year10 = 13 });
return source;
}

//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);
yearwiseProductValues.Add(source[row].Year5);
yearwiseProductValues.Add(source[row].Year6);
yearwiseProductValues.Add(source[row].Year7);
yearwiseProductValues.Add(source[row].Year8);
yearwiseProductValues.Add(source[row].Year9);
yearwiseProductValues.Add(source[row].Year10);


multiKeyDictionary.Add(source[row].ProductName, yearwiseProductValues);
return multiKeyDictionary;
}

//Pivot records
private static List<OutputData> Pivot(dynamic unpivotRecords)
{
int count = 10;
int startYear = 2016;
var pivotRecords = new List<OutputData>();

for (int i = 0; i < count; i++)
{
pivotRecords.Add(new OutputData
{
YKey = "Y" + (i + 1)
,
Year = startYear + i
,
Product1 = unpivotRecords.Product1["Product1"][i]
,
Product2 = unpivotRecords.Product2["Product2"][i]
,
Product3 = unpivotRecords.Product3["Product3"][i]
,
Product4 = unpivotRecords.Product4["Product4"][i]
});
}
return pivotRecords;
}

#endregion
}


#region Data Structures
internal class OutputData
{
public string YKey { get; internal set; }
public int Year { get; internal set; }
public int Product1 { get; internal set; }
public int Product2 { get; internal set; }
public int Product3 { get; internal set; }
public int Product4 { 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; }
public int Year5 { get; internal set; }
public int Year6 { get; internal set; }
public int Year7 { get; internal set; }
public int Year8 { get; internal set; }
public int Year9 { get; internal set; }
public int Year10 { get; internal set; }
}

#endregion
}



At first we are flattening the records by using a Multikey dictionary (Dictionary<string, List<int>>) inside the Unpivot function. That record we are passing into the Pivot function to get teh desired output.

Comments or Responses

Login to post response