Visual Studios 2008 and SQL, creating a specific sort of Cross Tab

I need some direction on this.

I'm still fairly new to Visual Studios and SQL interaction.  I have a large table that I will be making several different statistical calculations on and then would like to place it in either a spreadsheet like form or a report in Visual Studios.  An example of my table would be as follows:
(Please note, there are more than districts than regions)

Region    District    Sex   Age   #Purchased   Type Purchased
   1              a           M        18         2                       w1
    1             b           F         21         3                       w2
    1             a           F         35          3                     w1
    1             a            F       25           1                     w1

I have created several stored procedures in SQL to get the COUNT from specific fields, and in some cases with filters  (COUNT(type purchased) FROM table WHERE #purchased = 2 AND SEX = M).  The stored procedures are all dynamic SQL so that should take care of any variables I need to change.  Everything here is working fine at the moment...

I will then use these results to bounce of the total number of records in the table to create percentages.  Pretty simple stuff...

I have two problems that I am running into though.

1)  I would like to run a SELECT DISTINCT DISTRICT WHERE Region = 1 type query and load it into an array.  I would then like to use those values to re-run my statistics queries with an additional filter so that I can breakdown the statistics by both region and district.  Is this possible?

2)  I am not entirely sure what would be the best way to present the data in Visual Studios.  Because of item #1, there could be 5 different districts in a region, or there could be 6 different districts, I need something scalable enough that it will add additional rows where needed.

I'm trying to be as clear as possible, even though I know it's quite vague.  I'm not necessarily looking for coding here, just a direction on what would be the best way to do this.

Thanks for the help!
gwarcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rushShahCommented:
hi..

do u want something like this??

select * from yourtable where Region = 1 AND District = 'a'

if this is so,
then return the resultset to .net and bind it to gridview.
0
gwarcherAuthor Commented:
No, I don't need table data populated into the dataset.  

I would need to take the resuts from my stored procedures:

SELECT COUNT(*) where SEX = 'F" and Region = 1 and District = 'a'  (returns 3)
then have an all counts SELECT COUNT(*) where Region = 1 AND District = 'a' (returns 4)

I would then take results 1 (3) and result 2 (4) and find the percentage of Females in the District (and Region)  Then take that number and populate it in order to create a spreadsheet like report that would read something like this

                             Sex                 Age                      Type Purchased                amount purchased
District               M     F            18-25      25+        w1               w2                1-2               3+
1                      25%   75%        50%     50%       15%           65%                85%            15%

And so on for the regions as well.

Does that clarify things a bit?
0
rushShahCommented:
hi..
may be you can do something like this..in ur procedure..

SELECT (100 - F ) AS 'M', F
FROM (
SELECT ((SELECT COUNT(*) where SEX = 'F" and Region = 1 and District = 'a')
/(SELECT COUNT(*) where Region = 1 AND District = 'a') * 100)  AS 'F'
) t
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gwarcherAuthor Commented:
I will certainly try that.  But at this point I am more concerned with how to properly dynamically display the results in Visual Studios, whether it be in a form, report or some sort of data grid.  I need to be able to make calculations on the district, and then the same calculations again for each respective region in the district.
0
rushShahCommented:
you can display data in Page may be either using data grid...as in format as you need

you can make all calculation in sql server just as format as you want to display and then retrieve it in visual studio
or in visual studio and loop through the data to make calculations..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.