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, specially for those using metadata to retrieve information from a database, or for those who can not use Pivot transformation from SQL Server 2005.

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.

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:

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)

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.

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.

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

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..

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.

I've tried to use a tolerance using the ABS function or BETWEEN and surprisingly, none of the table fills in when I do that.
Oddly enough the two statements below both return no rows, when an equal sign does about half the time in my dataset.
rows = table->Select("ABS(" + columnY + "-" + System::String::Format( "{0:f15}", columnYValues[n]->ToString() ) + ") <.01" );
rows = table->Select("ABS(" + columnY + "-" + System::String::Format( "{0:f15}", columnYValues[n]->ToString() ) + ") >.01" );

Since I currently have only one of the two headers with decimal values, my hopefully temporary solution is to switch the columnX and Y and then transpose my results.

Thanks... I made changes so that the code will work when columnY contains decimal values. This may be more efficient in terms of the # of comparisons needed, but the memory efficiency without the filtering will be much poorer I think. However, the code will now work when both columnX and columnY have decimal values.

Here is VB.NET conversion of the above, plus a few improvements
The ZColumn can be Count or Sum, and then displayed as a percentage of Row,Column or Grand Totals
Also in this example the Row,Column and Grand Totals are added to the output table: