SQL Help...
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.
|
