data format in grid

[Tom]

I'm tyring to have my data in a side by side format for each year so the
user can compare the data together.
Here is how my data is being returned to me from the database;

year sales gains loss
2007 20 1,000 200
2006 10 1500 150


I want to show the data like this:

2007 2006
sales 20 10
gains 1,000 1500
loss 1500 150


is this possible to do, and what would be the best way to do this?

 

[Mark Rae [MVP]]

Is this possible to do,

Yes.

and what would be the best way to do this?

Depends what options you have for modifying the way the data is returned to
you...

You don't say where the data is coming from, so I'm assuming it's SQL
Server. If you are able to modify the server query, do a Google for

"SQL Server" crosstab

Google will return many examples for transforming the data in the way that
you need.

If you can't modify the data source, then I'd suggest you investigate the
<asp:Repeater> control...

 

[Tom]

correct, its coming a SQL database.

Yes.

and what would be the best way to do this?

Depends what options you have for modifying the way the data is returned
to you...

You don't say where the data is coming from, so I'm assuming it's SQL
Server. If you are able to modify the server query, do a Google for

"SQL Server" crosstab

Google will return many examples for transforming the data in the way that
you need.

If you can't modify the data source, then I'd suggest you investigate the
<asp:Repeater> control...

 

[Mark Rae [MVP]]

Correct, it's coming a from SQL Server database.

No problem, then...

 

[Tom]

I can get the data out as

year sales gains loss
2007 20 1,000 200
2006 10 1500 150

and in my grid I have it showing as


sales 20
gains 1000
loss 1500


how can I add my 2006 data so it shows like this in either on grid or
repeater control

2006 2007
sales 10 10
gains 1500 1000
loss 10 150

and so on. I have a portion of it working but now I need the bottom portion
of this to work

 

[Mark Rae [MVP]]

I need the bottom portion of this to work

http://www.google.co.uk/search?sour..._en-GBGB252GB252&q="SQL+Server+2005"+crosstab

 

[Tom]

I'm looking at them now and it appears that they won't do what I want to do.

I have the table pivoting in my sql, its the seperation of the years that
I'm stuck on.