Thanks for Sloan's input.
Hello Igotyourdotnet,
As for the knowlege base article Sloan has mentioned, it provide a helper
class which can query Rows in DataTable, also there is another one
describing on write a helper to query distinct rows from DataTable:
#HOW TO: Implement a DataSet SELECT DISTINCT Helper Class in Visual C# .NET
http://support.microsoft.com/kb/326176/en-us
However, I think if we put all the calculation with the given DataTable at
ADO.NET layer(.net code), it will be quite complex and require large number
of code event involve those two existing helper classes.
As for the raw table structure you mentioned below:
SalesMan Sales Make
Smith 25,000 1
Smith 9500 10
Smith 72,252 1
Smith 125,000 5
Smith 25,252 10
Jones 65,000 5
Jones 12,500 10
Is it an physical table or view in the database ? if so, I suggest you
first structure the result set at database layer through T-SQL. e.g.
we use the following sql query to return distinct salesman and make list.
=========
select distinct salesman from salerecords
select distinct make from salerecords
===========
and use the following SQL query to return the sales and salesamount group
upon salesman and make:
=======
select salesman, count(sales) as totalsales_count_make, sum(sales) as
totalsales_amount_make from salerecords group by salesman, make order by
salesman desc
=========
the result set will look like below:
==================
salesman totalsales_count_make totalsales_amount_make
Smith 2 97252
Smith 1 125000
Smith 2 34752
Jones 1 65000
Jones 1 12500
=======================
Given the above records, you can loop through the rows and manually
calculate the value of columns in each row of the target table you want (as
below);
====================
SalesMan TotalSales TotalSalesAmount Chevy(10)
TotalSales BMW(1) TotalSales Bentley(5) TotalSales
==============
You first loop through all the distinct salesman and for each salesman,
calculate the column value according to above resultset(return by group sql
query).
In a word, what we need to do is put all the data into single DataTable so
that the GridView can simply bind to it. And put all the group and query
code in .NET layer will be much more expensive.
How do you think?
Please feel free to post here if you have any other ideas or concerns on
this.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
