C# Pivot Table - CodeProject

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, we will see two methods to retrieve Pivot and Inverted tables from a DataTable . These methods are very useful, especially for those using metadata to retrieve information from a database, or for those who cannot 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 inverted or a more complex pivot table.
The Pivot Methods

First - Simple Inversion

Read all data, and return all columns as lines and lines as columns.
The X axis column provided is used as the column header, and some columns 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);
            //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;
Second - Pivoting
It uses the idea of three axes to build the new table. The X axis column is the column with the Unique Values to build the columns header. The Y axis value is the column with the values to be displayed as the rows in the first column. The Z axis is the "value" and 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 in the process. The null value is provided in case there is the need to fill 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 "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)
                                drReturn[rowColumnTitle] = 
                                     Convert.ToDecimal(drReturn[rowColumnTitle]) + 
                                     Convert.ToDecimal(dr[columnZ]);
                            catch
                                drReturn[rowColumnTitle] = dr[columnZ];
                            drReturn[rowColumnTitle] = dr[columnZ];
            returnTable.Rows.Add(drReturn);
        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 below will be used as an example of the data source:
						EmployeeID 			OrderID 			Amount 			Cost 			Date 		
	
						Sam 			1 			25 			13 			01/10/2007 		
					Sam 			2 			512 			1 			02/10/2007 		
					Sam 			3 			512 			1 			03/10/2007 		
					Tom 			4 			50 			1 			04/10/2007 		
					Tom 			5 			3 			7 			03/10/2007 		
					Tom 			6 			78,75 			12 			02/10/2007 		
					Sue 			7 			11 			7 			01/10/2007 		
					Sue 			8 			2,5 			66,2 			02/10/2007 		
					Sue 			9 			2,5 			22 			03/10/2007 		
					Jack 			10 			6 			23 			02/10/2007 		
					Jack 			11 			117 			199 			04/10/2007 		
					Jack 			12 			13 			2,6 			01/10/2007 		
					Jack 			13 			11,4 			99,8 			03/10/2007 		
					Phill 			14 			37 			2,1 			02/10/2007 		
					Phill 			15 			65,2 			99,3 			04/10/2007 		
					Phill 			16 			34,1 			27 			02/10/2007 		
					Phill 			17 			17 			959 			04/10/2007 		
	
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" });
First - Simple Inversion
A column is provided and the DataTable has its "axes turned". This method makes it 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 the column "OrderID" as the title (X axis column), we should use the code below:
//For example, for the DataTable  provided as Datasource 
DataTable dtReturn = GetInversedDataTable(dt, "OrderID");
The return table will be like the one below:
Second - Pivot Table
The three 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:
						EmployeeID 			01/12/2007 			02/12/2007 			03/12/2007 			04/12/2007 		
	
						Sam 			13 			1 			1 			- 		
					Tom 			- 			12 			7 			1 		
					Sue 			7 			66,2 			22 			- 		
					Jack 			2,6 			23 			99,8 			199 		
					Phill 			- 			27 			- 			959 		
	
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
I didn't use it, but It's useful to see this approach.

I use two arrays for row/column names and a matrix for values.

I actually have a list of pivot tables where in each one the names/columns may differ, this makes it even more interesting  

Sign in·View Thread 
namespace YMOReporting.Areas.Reportings.Controllers
    public class Proc1Controller : Controller
        private TransactDBContext db = new TransactDBContext();
        // GET: Reportings/Proc1
        public ActionResult Index()
            return View();
        /// <summary>
        /// Gets a Inverted DataTable
        /// </summary>
        /// <param name="Proc1_Result">Provided DataTable</param>
        /// <param name="Week">X Axis Column</param>
        /// <param name="Planner">Y Axis Column</param>
        /// <param name="Value">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 Proc1_Result, string Week,
             string Planner, string Value, string nullValue, bool sumValues)
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();
            if (Week == "")
                Week = Proc1_Result.Columns[0].ColumnName;
            //Add a Column at the beginning of the table
            returnTable.Columns.Add(Planner);
            //Read all DISTINCT values from Week Column in the provided DataTale
            List<string> WeekValues = new List<string>();
            foreach (DataRow dr in Proc1_Result.Rows)
                string WeekTemp = dr[Week].ToString();
                if (!WeekValues.Contains(WeekTemp))
                    //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.
                    WeekValues.Add(WeekTemp);
                    returnTable.Columns.Add(WeekTemp);
            //Verify if Y and Z Axis columns re provided
            if (Planner != "" && Value != "")
                //Read DISTINCT Values for Y Axis Column
                List<string> PlannerValues = new List<string>();
                foreach (DataRow dr in Proc1_Result.Rows)
                    if (!PlannerValues.Contains(dr[Planner].ToString()))
                        PlannerValues.Add(dr[Planner].ToString());
                //Loop all Column Y Distinct Value
                foreach (string PlannerValue in PlannerValues)
                    //Creates a new Row
                    DataRow drReturn = returnTable.NewRow();
                    drReturn[0] = PlannerValue;
                    //foreach column Y value, The rows are selected distincted
                    DataRow[] rows = Proc1_Result.Select(Planner + "='" + PlannerValue + "'");
                    //Read each row to fill the DataTable
                    foreach (DataRow dr in rows)
                        string rowColumnTitle = dr[Week].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)
                                        drReturn[rowColumnTitle] =
                                             Convert.ToDecimal(drReturn[rowColumnTitle]) +
                                             Convert.ToDecimal(dr[Value]);
                                    catch
                                        drReturn[rowColumnTitle] = dr[Value];
                                    drReturn[rowColumnTitle] = dr[Value];
                    returnTable.Rows.Add(drReturn);
                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;
Sign in·View Thread 
Thanks for the code, it is very usefull.

I have just one question, is it possible to sort the columns with Pivot option.

I have created a Datatable how looks like this

Articlenr  | 600 | 210 | 300 | 900

xxx        |  1  |  1  |  1  |  1

yyy        |  1  |  1  |  1  |  1

zzz        |  1  |  1  |  1  |  1

and it should be like this

Articlenr  | 210 | 300 | 600 | 900

xxx        |  1  |  1  |  1  |  1

yyy        |  1  |  1  |  1  |  1

zzz        |  1  |  1  |  1  |  1

Thanks for the feedback

Sign in·View Thread 
While I am not a C# programmer, I was able to convert your code into VB.NET and it worked like a charm.  Thank you so much.

Sign in·View Thread 
r the post. Current code displays a simple pivot table.

I am trying to create a pivot table that has 2 X-Axis fields and 2 Y-Axis fields? How to modify this code to achieve this.

Thanks,

sarathi

Sign in·View Thread 
ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns 
	Jean-Francois Lanouette30-Mar-12  4:00
        #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(row[zAxisFields[z]]);
                    break;
            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;
                        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);
                    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]);
                        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;
                    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);
                    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)]);
                        dataRowTotals[x] = Convert.ToString(xTotals[xTotals.GetUpperBound(0)]);
            table.Rows.Add(dataRowTotals);
            return table;
        #endregion Public Methods
Sign in·View Thread 
Re: ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns 
	Anurag Gandhi16-Apr-12  2:56





    
You may wish to see this[^] also.

Anurag Gandhi. 
http://www.gandhisoft.com 
Life is a computer program and every one is the programmer of his own life. 
My latest article: Group GridView Data

Sign in·View Thread 
Re: ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns 
	Jean-Francois Lanouette16-Apr-12  3:07
Re: ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns 
	Anurag Gandhi16-Apr-12  23:19
The PivotData Method returns DataTable only.  

Please elaborate in case u need something else.

Anurag Gandhi. 
http://www.gandhisoft.com 
Life is a computer program and every one is the programmer of his own life. 
My latest article: Group GridView Data

Sign in·View Thread 
A small team of reporting analysts stared at the screen in awe today here in Sydney Australia watching a SqlDataSource being transposed beautifully into a pivot style gridview.

I quote "This is an absolute cracker!"

Thank you so much for posting this Felipe.

Best Regards

Adrian

Sign in·View Thread 
Hi felipesabino,

I am using your function for pivoting several times in my app.

 DataTable dtEmp = objEmployee.GetEmployeeGroupwiseCount(GrpId);

 DataTable dtReturn = GetInversedDataTable(dtEmp, "DesignationName", "SubGroup", "EmpCount", " ", true);

What I want is,I need SubGroupId which is avalable in dtEmp.

I needed this because,there may be duplcate names for SubGroup so I can identify them by ID.

Is there any way I can get the SubGroupId along with SubGroupName.

Also is it possible to have Row-wise and columnwise count(of course except first column)

Thanks

modified on Friday, October 22, 2010 6:57 AM


Sign in·View Thread 
I am glad this article was useful for you!

Regarding your question, I believe there will be no way to do what you want just using the method...

What you can do is use the SubGroupId instead of the SubGroup when calling the method, so, after properly pivoting your data using the SubGroupId you can go through the datatable and change each subgroupid to its name.

DataTable dtReturn = GetInversedDataTable(dtEmp, "DesignationName", "SubGroupId", "EmpCount", " ", true);
foreach(DataRow row in dtReturn.Rows)
    string subGroupId = row[0].ToString();
    string subGroupName = SomeMethodOrCodeToGetYouGoupNameByTheId(subGroupId);
    row[0] = subGroupName;
Hope that was helpeful.

Cheers,

Felipe Sabino


Sign in·View Thread 
Hmm that is one way.Thanks.

I modified the method a little to add the id column with value.

Please check.It worked for me.Let me know if this can go wrong.

Still trying to get row-wise and column-wise sum.

  public static DataTable GetInversedDataTable(DataTable table,string myColumn, 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);

            returnTable.Columns.Add(myColumn);

            //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>();

                List<string> columnMyValues = new List<string>();

                foreach (DataRow dr in table.Rows)

                    if (!columnYValues.Contains(dr[columnY].ToString()))

                        columnYValues.Add(dr[columnY].ToString());

                foreach (DataRow dr in table.Rows)

                    if (!columnMyValues.Contains(dr[myColumn].ToString()))

                           columnMyValues.Add(dr[myColumn].ToString());

                List<string> mycolumnValues = new List<string>();

                int i = 0;

                //Loop all Column Y Distinct Value

                foreach (string columnYValue in columnYValues)

                    //Creates a new Row

                    DataRow drReturn = returnTable.NewRow();

                    drReturn[0] = columnYValue;

                    drReturn[1] = columnMyValues[i];

                    //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)

                                        drReturn[rowColumnTitle] =

                                             Convert.ToDecimal(drReturn[rowColumnTitle]) +

                                             Convert.ToDecimal(dr[columnZ]);

                                    catch

                                        drReturn[rowColumnTitle] = dr[columnZ];

                                    drReturn[rowColumnTitle] = dr[columnZ];

                    returnTable.Rows.Add(drReturn);

                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;

Sign in·View Thread 
after lots of time googling,I came to this post and finally I get a very nice solution.

Thank you very much.Cheers 

Sign in·View Thread 
I've modified your code to C++ and made a small change, so that the headers are sorted based on numerical value when applicable.

When I tried using the code using an example with decimal headers, the appropriate row and column headers appeared sorted by numerical value.

However, there were rows and columns that were all missing.  I think this has to do with the formatting of strings not displaying enough digits to satisfy the equality test in the filter.  Do you have an idea how to fix this?  I am not that experienced with managed code to test for a decimal System::Object and format the string to say 15 decimals if the object is a decimal, real, float, etc..

Sign in·View Thread 
You have an interesting point but I guess the way the algorithm was implemented you won't be able to use any float point object (as decimal, real, float) as the Table Header...

The ColumnName property of Datacolumn class is a string and it is the one the framework uses to do the filtering when using the DataTable.Select() Method.

A simple solution to your problem would be creating a new column in the source DataTable that has the same values as the float point column that you want to use as header but converted to string with the amount of decimal cases that you would like to consider.

Thanks,

Felipe Sabino

Felipe Sabino


Sign in·View Thread 
				Web03 
				2.8:2024-07-22:1
EmployeeID	OrderID	Amount	Cost	Date
`Sam`	`1`	`25`	`13`	`01/10/2007`
`Sam`	`2`	`512`	`1`	`02/10/2007`
`Sam`	`3`	`512`	`1`	`03/10/2007`
`Tom`	`4`	`50`	`1`	`04/10/2007`
`Tom`	`5`	`3`	`7`	`03/10/2007`
`Tom`	`6`	`78,75`	`12`	`02/10/2007`
`Sue`	`7`	`11`	`7`	`01/10/2007`
`Sue`	`8`	`2,5`	`66,2`	`02/10/2007`
`Sue`	`9`	`2,5`	`22`	`03/10/2007`
`Jack`	`10`	`6`	`23`	`02/10/2007`
`Jack`	`11`	`117`	`199`	`04/10/2007`
`Jack`	`12`	`13`	`2,6`	`01/10/2007`
`Jack`	`13`	`11,4`	`99,8`	`03/10/2007`
`Phill`	`14`	`37`	`2,1`	`02/10/2007`
`Phill`	`15`	`65,2`	`99,3`	`04/10/2007`
`Phill`	`16`	`34,1`	`27`	`02/10/2007`
`Phill`	`17`	`17`	`959`	`04/10/2007`
EmployeeID	01/12/2007	02/12/2007	03/12/2007	04/12/2007
`Sam`	`13`	`1`	`1`	`-`
`Tom`	`-`	`12`	`7`	`1`
`Sue`	`7`	`66,2`	`22`	`-`
`Jack`	`2,6`	`23`	`99,8`	`199`
`Phill`	`-`	`27`	`-`	`959`