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.
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.
public static DataTable GetInversedDataTable(DataTable table, string columnX,
params string[] columnsToIgnore)
{
DataTable returnTable = new DataTable();
if (columnX == "")
columnX = table.Columns[0].ColumnName;
returnTable.Columns.Add(columnX);
List<string> columnXValues = new List<string>();
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();
if (!columnXValues.Contains(columnXTemp))
{
columnXValues.Add(columnXTemp);
returnTable.Columns.Add(columnXTemp);
}
else
{
throw new Exception("The inversion used must have " +
"unique values for column " + columnX);
}
}
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);
}
}
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.
public static DataTable GetInversedDataTable(DataTable table, string columnX,
string columnY, string columnZ, string nullValue, bool sumValues)
{
DataTable returnTable = new DataTable();
if (columnX == "")
columnX = table.Columns[0].ColumnName;
returnTable.Columns.Add(columnY);
List<string> columnXValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
string columnXTemp = dr[columnX].ToString();
if (!columnXValues.Contains(columnXTemp))
{
columnXValues.Add(columnXTemp);
returnTable.Columns.Add(columnXTemp);
}
}
if (columnY != "" && columnZ != "")
{
List<string> columnYValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
if (!columnYValues.Contains(dr[columnY].ToString()))
columnYValues.Add(dr[columnY].ToString());
}
foreach (string columnYValue in columnYValues)
{
DataRow drReturn = returnTable.NewRow();
drReturn[0] = columnYValue;
DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");
foreach (DataRow dr in rows)
{
string rowColumnTitle = dr[columnX].ToString();
foreach (DataColumn dc in returnTable.Columns)
{
if (dc.ColumnName == rowColumnTitle)
{
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 (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:
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
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:
History
- 10/12/2007 - First release