Help with tables & SQL behind Matrix-like GridView

Question

Post reply

Help with tables & SQL behind Matrix-like GridView

dba321

Hall of Fame

Points: 3917
More actions
April 21, 2006 at 7:48 am

#152926

Is there anyway to accomplish this sketch I created (http://www.webfound.net/graph_grid.jpg) in a GridView without having to create a field for each graphname?
Customer_Graphs
---------------------
CustomerID
GraphName1
GraphName2
GraphName3
GraphName4
I'd prefer to use a Graph table
GraphName
-------------
GraphID
GraphName
Then in the Customer_Graphs reference the ID instead. The problem is I'd have to create 4 records for each customer if the customer_graph table was like this:
Customer_Graphs
---------------------
CustomerID
GraphID
This won't work in creating the GridView I want shown above. I'm not sure how I'd form the SQL then incorporate in a GridView to obtain the GridView I want in this situation

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 1

Try a case statement

Something like this:

SELECT Customer,

SUM(CASE graphname = 'Current Month Collections' THEN do something END) AS 'Current Month Collections'

FROM table

Keep in mind this is for display purposes.

Thanks,

dba321 Hall of Fame Points: 3917 More actions · Answer 2

yea, indeed that may work. Then I can just figure out the code to get the GridView to somehow post the results to a table...thanks.

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 3

The way I usually accomplish Matrix type tables or X-Tabs is I usually have a sproc creating this via a DTS package. Now you have a X-TAB table. For the grid create a business object that calls the sproc that calls the table and bind the grid, something like this:

With dgParts

.DataSource = Parts.RetrieveRequestorCurrentQuoteParts(rSQL.ToString) [BUSINESS OBJECT CALLER]

.DataBind()

End With

The above displays the matrix(x-tab) table, and you can implement CRUD methods depending on what your grid control provides. I use Infragistics (appears to be the most straightforward)