|
Subject:
|
SQL Help...
|
|
Posted By:
|
Ben
|
Post Date:
|
9/30/2004 4:07:58 AM
|
I have a table which contains three fields one a fk, one a category (Together they make a composite pk), and finally a value thats associated to the category. Each fk can have up to 9 category's with values, if a category has no value no entry would be made (Every fk will have at least 1 category).
Essientially I need to CrossTab this table, so I have the fk as a column with nine category columns filled with their values. I figured as we always want to report all nine categories and the number of categories is static, the most apt way of doing this would be a case statement with coalesce to treat the nulls, however I need to do this in an access data project which doesn't support the case statement 
The quickest way I can think to do this now is through nine subqueries breaking the original table into nine new tables based on the categories - I figure this is quicker than doing 9 outer joins based on the original table and a Where Not Category = 1 or category = 2, etc. Can anyone confirm I'm going the right way?
I guess the other question I'd like to ask is if there would be a gain saving the subqueries as views and reference them, or specifying them all in one big query.
|
|
Reply By:
|
Justin Cave
|
Reply Date:
|
10/12/2004 12:55:18 AM
|
Can you use DECODE rather than CASE?
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
|
|
Reply By:
|
sal
|
Reply Date:
|
10/12/2004 12:16:34 PM
|
If this is a Access Data Project, you may get away with creating a pivot table form. The form will do the case for you.
Just make sure to index the tables properly or you will degrade server performance either way you do this.
Sal
|
