Matrix form of a Dataset?

[TheMagician]

Hi

I have an asp.net webform which has a datagrid and a few other controls. Im 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