Simple & Advanced Pivots with C# and ASP.NET

Datatable version

Was pretty useful thanks, here the same code is presented but returning a DataTable instead

Also I've added the following: 

• Passing the column headers as parameters
• Prevent the crash if there's null values
• Possibility to have multiple values for the same x,y  
• Multiple Z columns in 2d table

using System;
using System.Data;
using System.Collections;

namespace PivotTable
{
    /// <summary>
    /// Create simple and advanced pivot reports.
    /// </summary>
    public class PivotTable
    {
        #region Variables

        private DataTable _DataTable;

        #endregion Variables

        #region Constructors

        public PivotTable(DataTable dataTable)
        {
            _DataTable = dataTable;
        }

        #endregion Constructors

        #region Properties

        public DataTable ResultTable
        {
            get { return _DataTable; }
        }
        #endregion Properties

        #region Private Methods

        private string[] FindValues(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string[] zAxisFields)
        {
            int zAxis = zAxisFields.Length;
            if (zAxis < 1)
                zAxis++;
            string[] zAxisValues = new string[zAxis];
            //set default values
            for (int i = 0; i <= zAxisValues.GetUpperBound(0); i++)
            {
                zAxisValues[i] = "0";
            }


            foreach (DataRow row in _DataTable.Rows)
            {
                if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
                {
                    for (int z = 0; z < zAxis; z++)
                    {
                        zAxisValues[z] = Convert.ToString(Convert.ToDecimal("0" + row[zAxisFields[z]]) + Convert.ToDecimal("0" +zAxisValues[z]));
                    }
                 //   break; // If you are sure that you don't have duplicated row of data, uncomment to gain performance
                }
            }

            return zAxisValues;
        }

        private string FindValue(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string zAxisField)
        {
            string zAxisValue = "";

            foreach (DataRow row in _DataTable.Rows)
            {
                if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
                {
                    zAxisValue = Convert.ToString(row[zAxisField]);
                    break;
                }
            }

            return zAxisValue;
        }
        #endregion Private Methods

        #region Public Methods

        /// <summary>
        /// Creates an advanced 3D Pivot table.
        /// </summary>
        /// <param name="xAxisField">The main heading at the top of the report.</param>
        /// <param name="yAxisField">The heading on the left of the report.</param>
        /// <param name="zAxisFields">The sub heading at the top of the report.</param>
        /// <param name="mainColumnName">Name of the column in xAxis.</param>
        /// <param name="columnTotalName">Name of the column with the totals.</param>
        /// <param name="rowTotalName">Name of the row with the totals.</param>
        /// <param name="zAxisFieldsNames">Name of the columns in the zAxis.</param>
        /// <returns>HtmlTable Control.</returns>
        public DataTable Generate(string xAxisField, string yAxisField, string[] zAxisFields, string mainColumnName, string columnTotalName, string rowTotalName, string[] zAxisFieldsNames)
        {

            /*
             * The x-axis is the main horizontal row.
             * The z-axis is the sub horizontal row.
             * The y-axis is the left vertical column.
             */

            //get distinct xAxisFields
            ArrayList xAxis = new ArrayList();
            foreach (DataRow row in _DataTable.Rows)
            {
                if (!xAxis.Contains(row[xAxisField]))
                    xAxis.Add(row[xAxisField]);
            }

            //get distinct yAxisFields
            ArrayList yAxis = new ArrayList();
            foreach (DataRow row in _DataTable.Rows)
            {
                if (!yAxis.Contains(row[yAxisField]))
                    yAxis.Add(row[yAxisField]);
            }

            //create a 2D array for the y-axis/z-axis fields
            int zAxis = zAxisFields.Length;
            if (zAxis < 1)
                zAxis = 1;
            string[,] matrix = new string[(xAxis.Count * zAxis), yAxis.Count];

            for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis fields
            {
                //rows
                for (int x = 0; x < xAxis.Count; x++) //loop thru x-axis fields
                {
                    //main columns
                    //get the z-axis values
                    string[] zAxisValues = FindValues(xAxisField, Convert.ToString(xAxis[x])
                                                      , yAxisField, Convert.ToString(yAxis[y]), zAxisFields);
                    for (int z = 0; z < zAxis; z++) //loop thru z-axis fields
                    {
                        //sub columns
                        matrix[(((x + 1) * zAxis - zAxis) + z), y] = zAxisValues[z];
                    }
                }
            }

            //calculate totals for the y-axis
            decimal[] yTotals = new decimal[(xAxis.Count * zAxis)];
            for (int col = 0; col < (xAxis.Count * zAxis); col++)
            {
                yTotals[col] = 0;
                for (int row = 0; row < yAxis.Count; row++)
                {
                    yTotals[col] += Convert.ToDecimal("0" + matrix[col, row]);
                }
            }

            //calculate totals for the x-axis
            decimal[,] xTotals = new decimal[zAxis, (yAxis.Count + 1)];
            for (int y = 0; y < yAxis.Count; y++) //loop thru the y-axis
            {
                int zCount = 0;
                for (int z = 0; z < (zAxis * xAxis.Count); z++) //loop thru the z-axis
                {
                    xTotals[zCount, y] += Convert.ToDecimal("0" + matrix[z, y]);
                    if (zCount == (zAxis - 1))
                        zCount = 0;
                    else
                        zCount++;
                }
            }
            for (int xx = 0; xx < zAxis; xx++) //Grand Total
            {
                for (int xy = 0; xy < yAxis.Count; xy++)
                {
                    xTotals[xx, yAxis.Count] += xTotals[xx, xy];
                }
            }

            //Build HTML Table
            //Append main row (x-axis)
            DataTable table = new DataTable();
            DataColumn columnYTitle = new DataColumn(mainColumnName);
            table.Columns.Add(columnYTitle);

            for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
            {
                if (x < xAxis.Count)
                {
                      for (int z = 0; z < zAxis; z++)
                      {
                          DataColumn column = new DataColumn();
                          column.ColumnName = Convert.ToString(xAxis[x] + " - " + zAxisFieldsNames[z]);
                          table.Columns.Add(column);
                      }
                }
                else
                {
                    for (int z = 0; z < zAxis; z++)
                    {
                        DataColumn column = new DataColumn();
                        column.ColumnName = columnTotalName + " - " + zAxisFieldsNames[z];
                        table.Columns.Add(column);
                    }
                }
            }

            
            //Append table items from matrix
            for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis
            {
                DataRow dataRow = table.NewRow();
                for (int z = 0; z <= (zAxis * xAxis.Count); z++) //loop thru z-axis + 1
                {
                    if (z == 0)
                    {
                        dataRow[z] = Convert.ToString(yAxis[y]);
                    }
                    else
                    {
                        dataRow[z] = Convert.ToString(matrix[(z - 1), y]);
                    }
                }

                //append x-axis grand totals
                for (int z = zAxis * xAxis.Count; z < zAxis + (zAxis * xAxis.Count); z++)
                {
                    dataRow[z + 1] = Convert.ToString(xTotals[z - (zAxis * xAxis.Count), y]);
                 
                }
                table.Rows.Add(dataRow);
            }

            //append y-axis totals
            DataRow dataRowTotals = table.NewRow();
            for (int x = 0; x <= (zAxis * xAxis.Count); x++)
            {
                if (x == 0)
                    dataRowTotals[0] = rowTotalName;
                else
                    dataRowTotals[x] = Convert.ToString(yTotals[x - 1]);
            }

            //append x-axis/y-axis totals
            for (int z = 0; z < zAxis; z++)
            {
                dataRowTotals[table.Columns.Count - zAxis + z] = Convert.ToString(xTotals[z, xTotals.GetUpperBound(1)]);
            }
            table.Rows.Add(dataRowTotals);

            return table;
        }

        /// <summary>
        /// Creates a simple 3D Pivot Table.
        /// </summary>
        /// <param name="xAxisField">The heading at the top of the table.</param>
        /// <param name="yAxisField">The heading to the left of the table.</param>
        /// <param name="zAxisField">The item value field.</param>
        /// <param name="mainColumnName">Title of the main column</param>
        /// <param name="columnTotalName">Title of the total column</param>
        /// <param name="rowTotalName">Title of the row column</param>
        /// <returns></returns>
        public DataTable Generate(string xAxisField, string yAxisField, string zAxisField, string mainColumnName, string columnTotalName, string rowTotalName)
        {
            return Generate(xAxisField, yAxisField, new string[0], new string[0], zAxisField, mainColumnName, columnTotalName, rowTotalName);
        }

        /// <summary>
        /// Creates a simple 3D Pivot Table.
        /// </summary>
        /// <param name="xAxisField">The heading at the top of the table.</param>
        /// <param name="yAxisField">The heading to the left of the table.</param>
        /// <param name="yAxisInfoFields">Other columns that we want to show on the y axis.</param>
        /// <param name="yAxisInfoFieldsNames">Title of the additionnal columns on y axis.</param>
        /// <param name="zAxisField">The item value field.</param>
        /// <param name="mainColumnName">Title of the main column</param>
        /// <param name="columnTotalName">Title of the total column</param>
        /// <param name="rowTotalName">Title of the row column</param>
        /// <returns></returns>
        public DataTable Generate(string xAxisField, string yAxisField, string[] yAxisInfoFields, string[] yAxisInfoFieldsNames, string zAxisField, string mainColumnName, string columnTotalName, string rowTotalName)
        {
            //style table
            /*
             * The x-axis is the main horizontal row.
             * The z-axis is the sub horizontal row.
             * The y-axis is the left vertical column.
             */

            //get distinct xAxisFields
            ArrayList xAxis = new ArrayList();
            foreach (DataRow row in _DataTable.Rows)
            {
                if (!xAxis.Contains(row[xAxisField]))
                    xAxis.Add(row[xAxisField]);
            }

            //get distinct yAxisFields
            ArrayList yAxis = new ArrayList();
            foreach (DataRow row in _DataTable.Rows)
            {
                if (!yAxis.Contains(row[yAxisField]))
                    yAxis.Add(row[yAxisField]);
            }

            //create a 2D array for the x-axis/y-axis fields
            string[,] matrix = new string[xAxis.Count, yAxis.Count];

            for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis fields
            {
                //rows
                for (int x = 0; x < xAxis.Count; x++) //loop thru x-axis fields
                {
                    //main columns
                    //get the z-axis values
                    string zAxisValue = FindValue(xAxisField, Convert.ToString(xAxis[x])
                                                  , yAxisField, Convert.ToString(yAxis[y]), zAxisField);



                    matrix[x, y] = zAxisValue;
                }
            }

            //calculate totals for the y-axis
            decimal[] yTotals = new decimal[xAxis.Count];
            for (int col = 0; col < xAxis.Count; col++)
            {
                yTotals[col] = 0;
                for (int row = 0; row < yAxis.Count; row++)
                {
                    yTotals[col] += Convert.ToDecimal("0" + matrix[col, row]);
                }
            }

            //calculate totals for the x-axis
            decimal[] xTotals = new decimal[(yAxis.Count + 1)];
            for (int row = 0; row < yAxis.Count; row++)
            {
                xTotals[row] = 0;
                for (int col = 0; col < xAxis.Count; col++)
                {
                    xTotals[row] += Convert.ToDecimal("0" + matrix[col, row]);
                }
            }
            xTotals[xTotals.GetUpperBound(0)] = 0; //Grand Total
            for (int i = 0; i < xTotals.GetUpperBound(0); i++)
            {
                xTotals[xTotals.GetUpperBound(0)] += xTotals[i];
            }

            //Build HTML Table

            //Build HTML Table
            //Append main row (x-axis)
            DataTable table = new DataTable();
            DataColumn columnYTitle = new DataColumn(mainColumnName);

            foreach (string yAxisInfoFieldsName in yAxisInfoFieldsNames)
            {
                table.Columns.Add(yAxisInfoFieldsName);
            }

            table.Columns.Add(columnYTitle);

            for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
            {
                if (x < xAxis.Count)
                {
                    DataColumn column = new DataColumn();
                    column.ColumnName = Convert.ToString(xAxis[x]);
                    table.Columns.Add(column);
                }
                else
                {
                    DataColumn column = new DataColumn(columnTotalName);
                    table.Columns.Add(column);
                }
            }

            //Append table items from matrix
            for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis
            {
                DataRow dataRow = table.NewRow();
                for (int z = 0; z <= xAxis.Count + yAxisInfoFieldsNames.Length; z++) //loop thru z-axis + 1
                {
                    if (z < yAxisInfoFieldsNames.Length)
                    {
                        dataRow[z] = Convert.ToString(_DataTable.Rows[y][yAxisInfoFields[z]]);
                    }
                    if (z == yAxisInfoFieldsNames.Length)
                    {
                        dataRow[z] = Convert.ToString(yAxis[y]);
                    }
                    if (z > yAxisInfoFieldsNames.Length)
                    {
                        dataRow[z] = Convert.ToString(matrix[(z - 1 - yAxisInfoFieldsNames.Length), y]);
                    }
                }


                dataRow[xAxis.Count + yAxisInfoFieldsNames.Length  + 1] = Convert.ToString(xTotals[y]);

                table.Rows.Add(dataRow);
            }

            //append y-axis totals
            DataRow dataRowTotals = table.NewRow();
            for (int x = 0; x <= (xAxis.Count + 1) + yAxisInfoFieldsNames.Length; x++)
            {
                if (x == 0)
                {
                    dataRowTotals[0] = rowTotalName;
                }
                if (x > yAxisInfoFieldsNames.Length)
                {
                    if (x <= xAxis.Count + yAxisInfoFieldsNames.Length)
                        dataRowTotals[x] = Convert.ToString(yTotals[(x - 1 - yAxisInfoFieldsNames.Length)]);
                    else
                        dataRowTotals[x] = Convert.ToString(xTotals[xTotals.GetUpperBound(0)]);
                }
            }
            table.Rows.Add(dataRowTotals);

            return table;
        }

        #endregion Public Methods
    }
}