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: