> 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