Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Sorting Columns with the C# Pivot Function

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:

Print | posted on Friday, April 20, 2007 12:05 PM | Filed Under [ CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

Feedback

Gravatar

# 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.
7/25/2007 10:14 PM | Andrew
Gravatar

# how to find tranpose of a data (rows and columns) in datagrid

How to interchange rows and colunms in a datagrid?
9/28/2007 5:56 AM | ndb
Gravatar

# 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.
8/1/2008 7:47 AM | Chris Haines
Gravatar

# 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.............
1/12/2009 12:31 AM | Kriti
Gravatar

# 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 :)
7/31/2010 7:12 AM | cricket match update
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET