Matrix form of a Dataset?

[TheMagician]

Hi

 

I have an asp.net webform which has a datagrid and a few other controls. I'm writing the code behind in vb.net

 

I currently have a dataset that returns:

 

Column1__Column2___Value

__A____.___X____.___10

__A____.___Y____.___20

__B____.___X____.___30

__B____.___Z____.___40

__C____.___Y____.___50

__C____.___Z____.___60

 

Which I can get to display perfectly well on a datagrid. What I want to display on the page is the matrix form of this data, ie in this case:

 

____A__.__B__._C

X__10__._30__._ 0

Y__20__._ 0__._50

Z__ 0__._40__._60

 

I have a few ideas but they all involve manually getting a list of the ABCs and XYZs, then manually iterating through the dataset to populate the datagrid or array with the matrix. This seems a little unweildy - has anyone got a better way of approaching this? (given the actual data may be returning as many as 35,000 records in the initial dataset, and the columns are going to be any two from a list of 7).

 

Many thanks for any ideas :)

 

Cheers

TM

[barski]

select column2,

sum(case column1 when 'a' then valuecol else 0 end) as 'A',
sum(case column1 when 'b' then valuecol else 0 end) as 'B',
sum(case column1 when 'c' then valuecol else 0 end) as 'C'
from tmptable
group by column2