Pivot and UnPivot in C#

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 -

1. How to perform PIVOT and UNPIVOT in C#
2. Creation of Multi-key dictionary
3. Read and Write in a Multi-key dictionary

Hope this will be useful. Thanks for reading. Zipped file attached.