Extra complex data layout.

I'm currently facing quite a complex situation, I have 3 tables:

Products
Groups
Stock Data

Stock Data has an external key to both the other tables so that I end up with

Products	Stock	Groups
ID	<- ID_Prods
	ID_Groups ->	ID

And it's pretty simple up to now

The hard part is showing it like I want...

Product Name	Other Product Cols (except the ID which I mean to hide)	Group 1 Name	Group 2 Name	...	Group N Name
Foo	More Foo Data	2	NULL	...	54
Bar	More BarData	42	10	...	NULL

Right now I'm using a simple function that builds this table on the fly, but it's slow, and must be called forth everytime I update ANYTHING on the three source tables.

Anybody has a suggestion on how to implement this Consider I'm gonna need this to display it in a read-only DataGridView.

Answer this question

Extra complex data layout.

Ron Loxton
what you ned is a "cross tab" query. In ms sql 2005 this is accomplished via the "PIVOT" operator (not available in TSQL 2000).
eeevans
err, allright, and suppose I don't have a sql server to back that up

is there a class that emulates it already done in vb

if not, how should I tackle this problem
AndrewvdMerwe
no db, I've got it all in a simple dataset, that I occasionally store as xml file.
katyad
what db are you using
Noah Coad

Do a google on crosstab +xml

there are some java examples, but they should be translatable.

Tomorrow, I am going to move this thread to the xml forum as it is not really about the control as opposed to xml processing.

We need to first solve the problem of transforming the input data to the proper shape (model).

Once in the model, we can easily bind to the control (view)

agree

If I get some time, I will look at some of the examples more closely. Again - If I get some time.

Email me a sample xml data dump.

cool

cheers!
Extra complex data layout.

Answer this question