(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