Using a "match" table to store multiple columns for parent data

[wfsmith]

Sorry for the confusing subject. Here's what im doing:

I have a table of products. Products have N categories and
subcategories. Right now its 4. But there could be more down the
line so it needs to be extensible.

So ive created a product table. Then a category table that has many
categories of products, of which a product can belong to N number of
these categories. Finally a ProductCategory "match" table.

This is pretty straigth forward. But im getting confused as to how to
write views/sprocs to pull out rows of products that list all the
products categories as columns in a single query view.

For example:

lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:
Cereal, Food for Kids, Crunchy food, and Boxed.

So we have:

Product
----------------
1 Capn Crunch

Categories
-----------------
1 Cereal
2 Food for Kids
3 Crunchy food
4 Boxed

ProductCategories
------------------
1 1
1 2
1 3
1 4

How do I go about writing a query that returns a single result set for
a view or data set (for use in a GridView control) where I would have
the following result:

Product results
---------------------------------
ProductId ProductName Category 1 Category 2
Category 3 Category N ...
------------------------------------------------------------------------
1 Capn Crunch Cereal Food for Kids Crunchy food
Boxed
Am I just thinking about this all wrong? Sure seems like it.

Cheers,

Will

[steve11228]

On Feb 27, 2:02 pm, [e-mail] wrote:

Sorry for the confusing subject. Here's what im doing:

If by chance you're still doing what you were doing (going in
circles?:) you can straighten out your trajectory with some help from
the Rac utility. Rac will easily produce any kind of dynamic crosstab
with no sql coding. We only require you to figure out what result you
want not how to do it :)
Visit Rac @
www.rac4sql.net

www.beyondsql.blogspot.com