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

.NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database.  Consider the result of a crosstab operation -- the columns returned will vary depending on the data.  There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes.  Also, in T-SQL there is no easy way to dynamically pivot data, and even doing it with hard-coded pivot values results in long, complicated SELECT statements.  So, in my opinion there is really is no benefit of doing this transformation within SQL Server.  (Unless you are in the mood for some fun dynamic sql and cursors ...)

Almost all modern report designers allow you to quickly and easily crosstab a standard recordset from SQL Server, so be sure to learn how to use this feature and take advantage of it.  Some are more flexible than others, but for the most part they work as you'd expect.  Either way, for report-writing crosstabing data at the presentation layer should not be a concern.

The main issue is programmers who are writing their own presentation layer.  Many programmers have difficulty writing code to do the transformation; I believe this may be why it is common to be tempted to force SQL Server to do all of the work.

Here's a quick example of an easy way to implement a crosstab at the presentation layer using C#. To keep things short and simple, I have just written a small static function that accepts any object that implements IDataReader and returns a crosstabbed DataTable object.  The code for the function is here.

All you need to do is open up an IDataReader object that returns the data you wish to Pivot.  The data should be fully summarized by SQL Server already -- i.e., you should GROUP BY your pivot column plus any other columns you wish to return, and you should aggregate your pivot value accordingly.  The DataReader should also be sorted so that all rows which will be pivoted into one are sorted together.  

For example, consider the Orders table in the Northwind database.  Suppose you want to display CompanyNames as rows, ProductNames as columns, and the total quantity of orders per CompanyName per ProductName in your crosstab.  The DataReader's recordset should be grouped by CompanyName and ProductName, it should include a SUM(Qty) calculation, and it should be sorted by CompanyName.  (see the example for this exact scenario using Northwind). 

What's nice is, this is all standard SQL and requires very little effort in terms of writing the SELECT and for the server to process and return the results.  We are letting the presentation layer handle the formatting.  Another benefit is that we can use this technique with any datasource that ADO.NET can connect to, not just SQL Server.  (In fact, we can do this with anything that implements IDataReader)

The arguments for the Pivot() function are as follows:

public static DataTable Pivot (IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)

  • dataValues -- this is any open DataReader object, ready to be transformed and pivoted into a DataTable.  As mentioned, it should be fully grouped, aggregated, sorted and ready to go. 
  • keyColumn -- This is the column in the DataReader which serves to identify each row.  In the previous example, this would be CustomerID.  Your DataReader's recordset should be grouped and sorted by this column as well.
  • pivotNameColumn -- This is the column in the DataReader that contains the values you'd like to transform from rows into columns.   In the example, this would be ProductName.
  • pivotValueColumn -- This is the column that in the DataReader that contains the values to pivot into the appropriate columns.  For our example, it would be Qty, which has been defined in the SELECT statement as SUM(Qty).

And, that's it.  You just call the Pivot() function on any object that implements IDataReader, specify the pivot information, and a DataTable is returned that you can use however you like.  The example uses this to fill up a DataGrid so you can see the results on a form.

Stay tuned for a performance comparison between this method and doing the crosstab transformation on the SQL Server .... You might be surprised by the results.


see also:

Print | posted on Wednesday, May 11, 2005 3:49 PM | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

Feedback

Gravatar

# re: .NET Cross Tabs: Transforming a Normalized IDataReader into a Pivoted DataTable

Great stuff Jeff!
5/11/2005 6:08 PM | Damian (Merkin)
Gravatar

# re: .NET Cross Tabs: Transforming a Normalized IDataReader into a Pivoted DataTable

You expected anything less?

Just make sure you give your Boss all the credit.....

5/12/2005 9:21 AM | Brett (Not just a Number...huh?)
Gravatar

# .NET CrossTabs versus SQL Server CrossTabs

5/13/2005 8:48 AM | Welcome to Jeff's Blog
Gravatar

# re: .NET Cross Tabs: Transforming a Normalized IDataReader into a Pivoted DataTable

Can I use this function to get multiple pivot columns, one for each product name. i.e

CustID Product1 Product2 Product3 Product4
1234 20 30 10 20


Woluld be great to see how the performance for such a thing varies!
5/13/2005 9:47 AM | vinny
Gravatar

# re: .NET Cross Tabs: Transforming a Normalized IDataReader into a Pivoted DataTable

uh.. yeah ... that's the whole point ! That's what it does.

For performance, read my latest post. And be sure to try the example posted in this one.
5/13/2005 9:56 AM | Jeff
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

How can I use this function if I want to pass a datatable in and then get a new table out.
Thanks
6/8/2005 1:43 PM | Peter
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

>>How can I use this function if I want to pass a datatable in and then get a new table out.

You probably shouldn't use this function for that purpose; it would be most efficient to use a DataReader and transform as you go. Filling up 1 datatable just to move it to another would be a waste of resources.

However, if you really need to, you pretty much just copy the code I've given only you replace the calls to the DataReader with a loop through the rows in the DataTable.

If there is a big demand for this or if it is not clear how to adjust the code to handle this, let me know and I'll post the code.
6/30/2005 8:50 PM | Jeff
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

This example was right on the money! Thanks you for posting it. Made my day!
8/17/2005 9:13 PM | J. Murphy
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Hi Jeff,

Please post the code for datatable.
Thanks
8/24/2005 1:22 PM | Karthik
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Great piece of code ! Thanks.

Is it possible to add more than one key column? Like I want to group by Year and then Month.

At the moment I am concatenating to achieve the result. Like shown below.

String SQL = "SELECT CONVERT(CHAR(4), Year) + '-' + CONVERT(char(2), Month) AS Month,PrincipalName, COUNT(PrincipalName) AS Transits " +
"FROM dbo.PrincipalStatQry " +
" GROUP BY ExitYear, ExitMonth, PrincipalName";


Please advise.
10/1/2005 9:24 AM | Fayeez
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable


With respect to the above posting, my question should be rather for adding pivotNameColumn.

Sorry for the inconvenience.
10/1/2005 9:28 AM | Fayeez
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Hi,

I've come across a small problem, not with this code but with formatting my results. My sql query produces the following:

UNIT AUD DATE ABSAB
Cheers 2005-10-01 00:00:00.000 29.64
Cheers 2005-10-02 00:00:00.000 249.37
Cheers 2005-10-07 00:00:00.000 44.44
Cheers 2005-10-08 00:00:00.000 28.36
Cheers 2005-10-09 00:00:00.000 196.24
City Edinburgh 2005-10-01 00:00:00.000 11.68
City Edinburgh 2005-10-03 00:00:00.000 69.09
City Edinburgh 2005-10-05 00:00:00.000 5.36
City Edinburgh 2005-10-07 00:00:00.000 27.89
City Edinburgh 2005-10-08 00:00:00.000 12.82
City Perth 2005-10-01 00:00:00.000 14.73

The sql query is Grouped by AudDate and Unit, Ordered by Unit and AudDate

The problem: Since there aren't entries for every day for each unit the columns don't appear in date order in the resulting datagrid.

Any suggestions? Let me know if I'm missing something really obvious.
11/9/2005 1:47 PM | William
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

William -- see my post on CROSS JOINS:

http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

In addition, you can always alter the code to hard-code the column names when the datatable is first created. The function is more of a simple framework that you can play with, and demonstration of how much easier it is to cross tab at the proper place (i.e., presentation).
11/9/2005 1:54 PM | Jeff S
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

You need this


if ( s == string.Empty ) {continue;}

before this

// assign the pivot values to the proper column; add new columns if needed:

s = dataValues[pNameIndex].ToString();


.... some times....
1/28/2006 9:10 PM | Alain
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Hi. I wanna to know is there any method to pack resources inside exe not using standart protocol res?
4/11/2006 1:59 AM | Randy
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Please convert the code to vb.net. Thanks
7/5/2006 4:54 AM | Romy
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

What is the performance over T-SQL
7/14/2006 1:42 AM | Vijay
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Vijay -- see this post:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

for some performance testing.
7/14/2006 8:21 AM | Jeff
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Great stuff, Jeff! helped me out a lot!
For those who were asking to have the input be a datatable instead of a datareader, I've converted the code below. Let me know if it can be cleaned up. Thx

public static DataTable Pivot(DataSet dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)
{
DataTable tmp = new DataTable();
DataRow r;
string LastKey = "//dummy//";
int i, pValIndex, pNameIndex;
string s;
bool FirstRow = true;

pValIndex = dataValues.Tables["THEOCOMP"].Columns[pivotValueColumn].Ordinal;
pNameIndex = dataValues.Tables["THEOCOMP"].Columns[pivotNameColumn].Ordinal;

for (i=0 ; i<=dataValues.Tables["THEOCOMP"].Columns.Count-1 ; i++)
{
if (i != pValIndex && i != pNameIndex )
tmp.Columns.Add(dataValues.Tables["THEOCOMP"].Columns[i].ColumnName, dataValues.Tables["THEOCOMP"].Columns[i].GetType());
}

r = tmp.NewRow();

foreach(DataRow row1 in dataValues.Tables["THEOCOMP"].Rows)
{
if (row1[keyColumn].ToString() != LastKey)
{
if (!FirstRow)
tmp.Rows.Add(r);

r = tmp.NewRow();
FirstRow = false;

//loop thru fields of row1 and populate tmp table
for (i=0 ; i<=row1.ItemArray.Length-3 ; i++)
r[i] = row1[tmp.Columns[i].ToString()];

LastKey = row1[keyColumn].ToString();
}

s = row1[pNameIndex].ToString();

if (!tmp.Columns.Contains(s))
tmp.Columns.Add(s, typeof(double));

r[s] = row1[pValIndex];
}

//add that final row to the datatable:
tmp.Rows.Add(r);

return tmp;
}
8/9/2006 10:06 AM | tl
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Hi All,

How do I update or move one record in column2 to column4 using C# .NET
5/28/2007 9:28 AM | Lindile
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

hep me!

how can i fill datagrid with data reader.

regards
asif
7/28/2007 2:52 AM | asif ghanchi
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

Is it applicable in web form?
2/25/2009 8:20 PM | malen
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

jeff...

how many maximum column that generate from this sp ?

i wonder that the result is under 80 column.. thats is true ?

btw nice working pal...!
12/15/2009 11:06 PM | jun
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET