Sorting Columns with the C# Pivot Function

Fri Apr 20, 2007 by Jeff Smith in crosstabs-pivoting-data, net-c-vb, code-library-c-vb-net

Time for another exciting edition of the mailbag!

Maxime writes:

Hi,

First of all, your class is really nice and it handles my problem really well.

I would like to ask you something about the class. I would like to order the pivot columns by column name but I'm unable to do that yet. Do you think you could help me with that ?

Thank you in advance for your time and sorry to bother you with this question but I've tried to post in forums but didn't get any results.

Maxime

(Note: Maxime is referring to my simple Pivot function discussed here. The idea is, as always, to let SQL focus on returning raw, summarized and filtered data in a standard row/column result, and to let your very capable and efficient presentation layer handle all formatting -- which also happens to include pivoting data from rows to columns.)

Sure thing, Maxime, that's a reasonable request and a great idea for improving the code.

If you are using .NET 2.0, you can easily re-arrange columns in a DataTable, but this is not so easy in 1.1. By using the setOrdinal method of a DataColumn, we can ensure that we are creating those new columns in the proper place.

All you need to do is take the existing code for the Pivot function shown here and change just this line:

if (!tmp.Columns.Contains(s))

tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex));

To:

if (!tmp.Columns.Contains(s))
{
DataColumn c = tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex));
// set the index so that it is sorted properly:
int newOrdinal = c.Ordinal;
for (i = newOrdinal - 1; i >= dataValues.FieldCount - 2; i--)
if (c.ColumnName.CompareTo(tmp.Columns[i].ColumnName) < 0)
newOrdinal = i;
c.SetOrdinal(newOrdinal);
}

That should result in "sorted" pivot columns. Use similar tricks if necessary to move other columns around in any DataTable as needed. To sort the columns in descending order, change the CompareTo() method to compare with > 0. In addition, keep in mind that this is a regular text comparison; if you need to sort numeric or date column headers, you should first convert those strings to proper datatypes before doing the compare, or at the very least to a string format that compares and sorts you way you'd like.

If you are using .NET 1.1, your only options are to a) move the data from this DataTable to another one, with columns that are properly sorted as the new table is created, or b) gather the column names in advance and pass those to the function and create the DataTable with those columns already added and ready to be filled in the proper order.

I hope this helps, and congratulations on writing simple, clear, short and efficient SQL code while letting your presentation layer do its job!

see also:

Legacy Comments


Andrew 2007-07-25	re: Sorting Columns with the C# Pivot Function Hi, I'm not sure about this part of the code: i >= dataValues.FieldCount - 2 I found that my columns were not getting sorted correctly. I made the following changes and it worked - First, after adding the columns the non-pivot and key columns to the destination table, I included this line: int numberOfNonPivotColumns = destination.Columns.Count; Then, I changed the condition mentioned above to: i >= numberOfNonPivotColumns This seems to work better. Thanks for this function, still the best one I could find on the net.

ndb 2007-09-28	how to find tranpose of a data (rows and columns) in datagrid How to interchange rows and colunms in a datagrid?

Chris Haines 2008-08-01	re: Sorting Columns with the C# Pivot Function Why not just cross-join in the SQL to ensure all the pivot columns are in the data? That way you can do a secondary sort on the pivot column.

Kriti 2009-01-12	re: Sorting Columns with the C# Pivot Function I have an datagrid in Id Name 1 a 2 b . . . . . . but i want Id 1 Name a Id 2 Name b ................ ............. .............. Please send me soon reply . How can we display an datagrid in this form.............

cricket match update 2010-07-31	re: Sorting Columns with the C# Pivot Function I hate programming and all the coding. Hats off to you guys who do this tough job :)