running a query that can create like a pivot in excel

I can't see how re-creating in Pivot tables in VB .net might work efficiently - other experts may disagree - but a pivot table is a dynamic implementation of set theory so you will need
a) storage of a data structure
b) a way of creating dynamic relationships on that data (the equivalent of using SQL to join a table to itself)
c) an implementation of one or more matrix translations and set theory algorithms (which is, again, how SQL handles queries)
d) a presentation layer to hide the complexity

Bearing in mind that MS and others have been figuring out how to do this efficiently  for a very long time your time might be better invested in looking for some 3rd party data management tools over which you can build your pivot interface (if its not already there).

Where is your data coming from?

I am getting my data from SQL SERVER, I would want it to display the fields in the database, user clicks of he field to express what should be the columns and the rows. the on pressing run. the data is populated into the sheet.

You state in your question, "I want user to select fields for columns and rows and the the system should populate the sheet according to the table structure ", are you looking to populate an Excel Sheet in a work book in a Excel document?

I have an farpoint spread in my application. on drag and drop of the fields for the rows and columns and specification of the fields for functions (sum, mean) the system should populate the cells in the spread sheet.

I will not be able to help with this. I have never worked with the FarPoint Spread product from GrapeCity and would not be able to tell how to achieve your needs.

Dear experts,

i think you can still help me,  My problem is not about populating data into the spread,  my challenge is with manipulating the data into a tabular format fast like the pivot tables in excel do.  I would want the user to drag fields for rows, columns and then select the analysis variable and booo!!!! the table is made. I think there is an alogolithim for this. What i am doing now is populate a class and then display contents of the class at the end.

Thank you

Anthony

To your statement, "I would want the user to drag fields for rows, columns and then select the analysis variable and booo!!!! the table is made.",

To this part of your statement, "user to drag fields for rows, columns", this is just a basic Drag Drop operation. From what type of object to what type of object?

To this part of your statement, "select the analysis variable", what does this even mean?

For example if I am doing income distribution by age group and education level. I will have the three variables   in say a list box or combobox. User selects age group for rows and then select education level for the column the select income and the table is made. This is done in excel pivoting and is very fast
Thank you
Anthony