Cross-tab / pivot / transform a DataTable

[David Jackson]

Hello,

Firstly I apologise if this is the wrong newsgroup.

I am looking for a way to cross-tab / transform / pivot the data in a
DataTable fetched from a SQL Server stored procedure. Unfortunately, due to
company restrictions, I'm unable to modify the stored procedure or create a
different one.

The data is historical exchange rate data and, when fetched from SQL Server,
looks like this:

FxDate IsoCc FxRate
=====================
2009-07-01 AED 6.0533
2009-07-02 AED 6.0117
2009-07-03 AED 6.0029
2009-07-01 AUD 2.0401
2009-07-02 AUD 2.0578
2009-07-03 AUD 2.0442
2009-07-01 CAD 1.8921
2009-07-02 CAD 1.9004
2009-07-03 CAD 1.8950
....
....
....

The date range and currency range are chosen by the users at run-time.

Ideally, I would like to transform the above data so that it has the
following structure:

FxDate AED AUD CAD
============================
2009-07-01 6.0533 2.0401 1.8921
2009-07-02 6.0117 2.0578 1.9004
2009-07-03 6.0029 2.0442 1.8950

This transformed data will then be used as the datasource of a databound
control. There is no aggregation involved, simply a transformed arrangement
of the data.

A Google search reveals lots of examples of how to do this e.g.
http://redsouljaz.wordpress.com/2009/06/22/asp-net-cross-tab-pivot-from-data-table/

But I'm wondering if this is really the only way to do this? Perhaps there
is a more efficient method available, maybe with LINQ?

N.B. I'm not expecting anyone to write my code for me, but I would be
grateful for any guidance with choosing the most efficient method.

Thank you.

DJ

 

[Gregory A. Beamer]

Hello,

Firstly I apologise if this is the wrong newsgroup.

I am looking for a way to cross-tab / transform / pivot the data in a
DataTable fetched from a SQL Server stored procedure. Unfortunately,
due to company restrictions, I'm unable to modify the stored procedure
or create a different one.

SNIPPED CODE

A Google search reveals lots of examples of how to do this e.g.
http://redsouljaz.wordpress.com/2009/06/22/asp-net-cross-tab-pivot-from
-data-table/

But I'm wondering if this is really the only way to do this? Perhaps
there is a more efficient method available, maybe with LINQ?

N.B. I'm not expecting anyone to write my code for me, but I would be
grateful for any guidance with choosing the most efficient method.

There are LINQ to SQL implementations you can find. The main methodology is
taking your result set and creating a new one by looping. MSDN has an
example where you hide this looping in extension methods, which is pretty
neat and makes it very easy to use LINQ. Stack Overflow has a few other
examples.

I am guessing you have to access the data via the stored procedure and
cannot simply write a new LINQ query to hit the data?


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

******************************************
| Think outside the box! |
******************************************

 

[David Jackson]

Gregory,

There are LINQ to SQL implementations you can find. The main methodology
is
taking your result set and creating a new one by looping. MSDN has an
example where you hide this looping in extension methods, which is pretty
neat and makes it very easy to use LINQ. Stack Overflow has a few other
examples.

Thank you for this. I will investigate.

I am guessing you have to access the data via the stored procedure and
cannot simply write a new LINQ query to hit the data?

Unfortunately not. This is a temporary assignment and I'm not allowed to use
anything other than the existing stored procedures.

DJ

 

[Gregory A. Beamer]

Unfortunately not. This is a temporary assignment and I'm not allowed
to use anything other than the existing stored procedures.

Untrusting control freaks! ;-)

I understand fully. I have been on contract (and even a full time employee)
in a place with very strong security controls.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

******************************************
| Think outside the box! |
******************************************

 

[David Jackson]

Untrusting control freaks! ;-)

I understand fully. I have been on contract (and even a full time
employee)
in a place with very strong security controls.

Yes!

The SQL DBA / designer is a malodorous gentleman with a beard, John Lennon
glasses and a cardigan who enjoys several pints of Real Ale at lunchtime,
keeps popping outside to smoke his pipe, and just can't understand why the
company had to move away from OpenVMS on DEC VAX. I've never seen so many
stereotypes in one person before

 

[Gregory A. Beamer]

The SQL DBA / designer is a malodorous gentleman with a beard, John
Lennon glasses and a cardigan who enjoys several pints of Real Ale at
lunchtime, keeps popping outside to smoke his pipe, and just can't
understand why the company had to move away from OpenVMS on DEC VAX.
I've never seen so many stereotypes in one person before

I should have dropped the smiley on my comment then. ;-)

Sounds like you have the beatnik dinosaur complex working in the
organization. And, he probably thinks he is saving his job by being a
control freak. ;-)



--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************