C# Pivot Table

• Download PivotTable - 7,295 KB
• Download PivotTable_source - 20,459 KB

Introduction

Pivot transformation is very useful to summarize Data in a flat data table (Columns and Rows), providing a more clean visualization of the data.

In this article it will be shown 2 methods to retrieve Pivot and Inverted tables from a DataTable.

This methods are very useful, specially for those using Metadata to retrieve information from database, or those that can not use PIVOT Transformation from SQL SERVER 2005

http://msdn2.microsoft.com/en-us/library/ms140308.aspx

The two methods are independent but complement each other in a way that data can be manipulated to return the table inversted or a more complex pivot table.

The Pivot Methods

1st - Simple Inversion

Read all Data and return all Columns as Lines and Lines as Columns.

The X Axis Column provided is used as Column Header and some Column may be ignored in the process if desired so.

        /// <summary>
        /// Gets a Inverted DataTable
        /// </summary>
        /// <param name="table">DataTable do invert</param>
        /// <param name="columnX">X Axis Column</param>
        /// <param name="nullValue">null Value to Complete the Pivot Table</param>
        /// <param name="columnsToIgnore">Columns that should be ignored in the pivot 
        /// process (X Axis column is ignored by default)</param>
        /// <returns>C# Pivot Table Method  - Felipe Sabino</returns>
        public static DataTable GetInversedDataTable(DataTable table, string columnX, 
                                                     params string[] columnsToIgnore)
        {
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();

            if (columnX == "")
                columnX = table.Columns[0].ColumnName;

            //Add a Column at the beginning of the table

            returnTable.Columns.Add(columnX);

            //Read all DISTINCT values from columnX Column in the provided DataTale
            List<string> columnXValues = new List<string>();

            //Creates list of columns to ignore
            List<string> listColumnsToIgnore = new List<string>();
            if (columnsToIgnore.Length > 0)
                listColumnsToIgnore.AddRange(columnsToIgnore);

            if (!listColumnsToIgnore.Contains(columnX))
                listColumnsToIgnore.Add(columnX);

            foreach (DataRow dr in table.Rows)
            {
                string columnXTemp = dr[columnX].ToString();
                //Verify if the value was already listed
                if (!columnXValues.Contains(columnXTemp))
                {
                    //if the value id different from others provided, add to the list of 
                    //values and creates a new Column with its value.
                    columnXValues.Add(columnXTemp);
                    returnTable.Columns.Add(columnXTemp);
                }
                else
                {
                    //Throw exception for a repeated value
                    throw new Exception("The inversion used must have unique values for column " + columnX);
                }
            }

            //Add a line for each column of the DataTable

            foreach (DataColumn dc in table.Columns)
            {
                if (!columnXValues.Contains(dc.ColumnName) && !listColumnsToIgnore.Contains(dc.ColumnName))
                {
                    DataRow dr = returnTable.NewRow();
                    dr[0] = dc.ColumnName;
                    returnTable.Rows.Add(dr);
                }
            }

            //Complete the datatable with the values
            for (int i = 0; i < returnTable.Rows.Count; i++)
            {
                for (int j = 1; j < returnTable.Columns.Count; j++)
                {
                    returnTable.Rows[i][j] = table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString();
                }
            }

            return returnTable;
        }

2nd - Pivoting

It used the Idea of 3 Axis to build the new table.

The X Axis Column is the column with the Unique Values to build the Columns Header

The Y Axis Values is the column with the values to be displayed as the Rows in the First Column.

The Z Axis is the "value". Is the match of X and Y in the DataSource and can be the sum of values if more than one different value is found n the process.

The Null Value is provided in case there is the need to fill the empty Cells of the Table with a Certain Value.

The flag to sum values is used in case there is more than one value for a certain X and Y Column combination, if it is provided as "false" the last value that is read is displayed.

       /// <summary>
        /// Gets a Inverted DataTable
        /// </summary>
        /// <param name="table">Provided DataTable</param>
        /// <param name="columnX">X Axis Column</param>
        /// <param name="columnY">Y Axis Column</param>
        /// <param name="columnZ">Z Axis Column (values)</param>
        /// <param name="columnsToIgnore">Whether to ignore some column, it must be 
        /// provided here</param>
        /// <param name="nullValue">null Values to be filled</param> 
        /// <returns>C# Pivot Table Method  - Felipe Sabino</returns>
        public static DataTable GetInversedDataTable(DataTable table, string columnX, 
             string columnY, string columnZ, string nullValue, bool sumValues)
        {
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();

            if (columnX == "")
                columnX = table.Columns[0].ColumnName;

            //Add a Column at the beginning of the table
            returnTable.Columns.Add(columnY);


            //Read all DISTINCT values from columnX Column in the provided DataTale
            List<string> columnXValues = new List<string>();

            foreach (DataRow dr in table.Rows)
            {

                string columnXTemp = dr[columnX].ToString();
                if (!columnXValues.Contains(columnXTemp))
                {
                    //Read each row value, if it's different from others provided, add to 
                    //the list of values and creates a new Column with its value.
                    columnXValues.Add(columnXTemp);
                    returnTable.Columns.Add(columnXTemp);
                }
            }

            //Verify if Y and Z Axis columns re provided
            if (columnY != "" && columnZ != "")
            {
                //Read DISTINCT Values for Y Axis Column
                List<string> columnYValues = new List<string>();

                foreach (DataRow dr in table.Rows)
                {
                    if (!columnYValues.Contains(dr[columnY].ToString()))
                        columnYValues.Add(dr[columnY].ToString());
                }

                //Loop all Column Y Distinct Value
                foreach (string columnYValue in columnYValues)
                {
                    //Creates a new Row
                    DataRow drReturn = returnTable.NewRow();
                    drReturn[0] = columnYValue;
                    //foreach column Y value, The rows are selected distincted
                    DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");

                    //Read each row to fill the DataTable
                    foreach (DataRow dr in rows)
                    {
                        string rowColumnTitle = dr[columnX].ToString();

                        //Read each column to fill the DataTable
                        foreach (DataColumn dc in returnTable.Columns)
                        {
                            if (dc.ColumnName == rowColumnTitle)
                            {
                                //If Sum of Values is True it try to perform a Sum
                                //If sum is not possible due to value types, the value 
                                // displayed is the last one read
                                if (sumValues)
                                {
                                    try
                                    {
                                        drReturn[rowColumnTitle] = 
                                             Convert.ToDecimal(drReturn[rowColumnTitle]) + 
                                             Convert.ToDecimal(dr[columnZ]);
                                    }
                                    catch
                                    {
                                        drReturn[rowColumnTitle] = dr[columnZ];
                                    }
                                }
                                else
                                {
                                    drReturn[rowColumnTitle] = dr[columnZ];
                                }
                                
                            }
                        }
                    }

                    returnTable.Rows.Add(drReturn);
                }

            }
            else
            {
                throw new Exception("The columns to perform inversion are not provided");
            }

            //if a nullValue is provided, fill the datable with it
            if (nullValue != "")
            {
                foreach (DataRow dr in returnTable.Rows)
                {
                    foreach (DataColumn dc in returnTable.Columns)
                    {
                        if (dr[dc.ColumnName].ToString() == "")
                            dr[dc.ColumnName] = nullValue;
                    }
                }
            }

            return returnTable;
        }

Using the code

The article provides two ways to perform a Pivot Transformation.

In both ways the table above will be used as an example of data source

The code below shows how to create the table above

DataTable dt = new DataTable();


dt.Columns.Add("EmployeeID", Type.GetType("System.String"));
dt.Columns.Add("OrderID", Type.GetType("System.Int32"));
dt.Columns.Add("Amount", Type.GetType("System.Decimal"));
dt.Columns.Add("Cost", Type.GetType("System.Decimal"));
dt.Columns.Add("Date", Type.GetType("System.String"));
dt.Rows.Add(new object[] { "Sam", 1, 25.00, 13.00, "01/10/2007" });
dt.Rows.Add(new object[] { "Sam", 2, 512.00, 1.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Sam", 3, 512.00, 1.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Tom", 4, 50.00, 1.00, "04/10/2007" });
dt.Rows.Add(new object[] { "Tom", 5, 3.00, 7.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Tom", 6, 78.75, 12.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Sue", 7, 11.00, 7.00, "01/10/2007" });
dt.Rows.Add(new object[] { "Sue", 8, 2.50, 66.20, "02/10/2007" });
dt.Rows.Add(new object[] { "Sue", 9, 2.50, 22.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Jack", 10, 6.00, 23.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Jack", 11, 117.00, 199.00, "04/10/2007" });
dt.Rows.Add(new object[] { "Jack", 12, 13.00, 2.60, "01/10/2007" });
dt.Rows.Add(new object[] { "Jack", 13, 11.40, 99.80, "03/10/2007" });
dt.Rows.Add(new object[] { "Phill", 14, 37.00, 2.10, "02/10/2007" });
dt.Rows.Add(new object[] { "Phill", 15, 65.20, 99.30, "04/10/2007" });
dt.Rows.Add(new object[] { "Phill", 16, 34.10, 27.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Phill", 17, 17.00, 959.00, "04/10/2007" });

1st - Simple Inversion:

A Column is provided and the DataTable has its "axes turned".

This method makes possible to ignore some columns during the inversion, so that the return table has only the columns of interest.

If we want to invert with column "OrderID" as Title (X Axis Column), we shoud use the code bellow.

//For example, for the DataTable  provided as Datasource 
DataTable dtReturn = GetInversedDataTable(dt, "OrderID");

The Return table will be like the one below

2nd - Pivot Table

The 3 columns are provided and a new DataTable is returned.

The Example below will use the Source table and the params below to build a Pivot Table

X Axis Column: "Date"

Y Axis Column: "EmployeeID"

Z Axis Column: "Cost"

Null Value: "-";

Sum of Values: true

//For example, for the DataTable  provided as Datasource 
DataTable dtReturn = GetInversedDataTable(dt, "Date", "EmployeeID", "Cost", "-", true);

The return table will be like the one below

Points of Interest

You can read more about Pivot Tables at

• http://en.wikipedia.org/wiki/Pivot_table
• http://books.slashdot.org/article.pl?sid=04/11/19/2012256
• http://j-walk.com/ss/excel/usertips/tip068.htm

History

10/12/2007 - First Release