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

(wfsmith@gmail.com) writes:
> 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.

As we shall see, you may want to wait with that part.

> 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:

SELECT P.ProductName,
Category1 = MAX(CASE C.rowno WHEN 1 THEN C.CategoryName END),
Category2 = MAX(CASE C.rowno WHEN 2 THEN C.CategoryName END),
Category3 = MAX(CASE C.rowno WHEN 3 THEN C.CategoryName END),
Category4 = MAX(CASE C.rowno WHEN 4 THEN C.CategoryName END)
FROM Products P
JOIN (SELECT C.CategoryName,
rowno = row_number() OVER(PARTITON BY PC.ProductID
ORDER BY C.CategoryName)
FROM ProductCategories PC
JOIN Categories C ON PC.CategoryID = C.CategoryID) AS C
ON P.ProductID = C.ProductID
GROUP BY P.ProductName

If you want more product categories, you will need to extend the query.
If you want to handle an unknown number of categories, you would need
to use dynamic SQL to build the query, considerably increasing the
complexity of the task.

The key point here is that a SELECT statement returns a table, and a
table always has a well-defined set of columns.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 27, 2:02 pm, wfsm...@gmail.com 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

> SELECT P.ProductName,
> Category1 = MAX(CASE C.rowno WHEN 1 THEN C.CategoryName END),
> Category2 = MAX(CASE C.rowno WHEN 2 THEN C.CategoryName END),
> Category3 = MAX(CASE C.rowno WHEN 3 THEN C.CategoryName END),
> Category4 = MAX(CASE C.rowno WHEN 4 THEN C.CategoryName END)
> FROM Products P
> JOIN (SELECT C.CategoryName,
> rowno = row_number() OVER(PARTITON BY PC.ProductID
> ORDER BY C.CategoryName)
> FROM ProductCategories PC
> JOIN Categories C ON PC.CategoryID = C.CategoryID) AS C
> ON P.ProductID = C.ProductID
> GROUP BY P.ProductName
>
> If you want more product categories, you will need to extend the query.
> If you want to handle an unknown number of categories, you would need
> to use dynamic SQL to build the query, considerably increasing the
> complexity of the task.

Thank you very much for the well-thought response Erland.

Performance-wise, would it be better to go this route or simply return
multiple record sets? E.g.

Select Products.* From Products
Select * from ProductCategories PC JOIN Categories C ON PC.CategoryID
= C.CategoryID

....and then just handle the different recordsets using a DataSet or
whatever on the code side?

I'm imagining a new table of, say, Vendors, or other tables that also
contain multiple rows of data that pertain to a single Product. The
above approach seems like it would get complex in a hurry.

Cheers

(wfsmith@gmail.com) writes:
> Performance-wise, would it be better to go this route or simply return
> multiple record sets? E.g.
>
> Select Products.* From Products
> Select * from ProductCategories PC JOIN Categories C ON PC.CategoryID
>= C.CategoryID
>
> ...and then just handle the different recordsets using a DataSet or
> whatever on the code side?

If I understand your scenario I don't think there is much difference
in performance, although SQL Server will have to do some more work
with my approach. But if you are to get multiple result sets, you
would have to bounce the data around in the client code, to get your
grids as desired. That sounds like a lot of coding to me.

Then again, if you want to support any number of categories, it may be
better to do the pivoting client-side.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx