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

(wfsmith@xxxxxxxxx) 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@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: howto use the query results as view column?
    ... how can create a view like this (id, name, category1, category2, ... category3) with high performance? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Hierachical Relationship driving me nuts.
    ... If you are using SQL Server 2005, ... I need to then be able to retrieve a particular category and its children (to a specified depth) and display it in a repeater. ... ---category1 child ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL or programming?
    ... Selecting the category which occurs the most for items in Table B. So if ... category1 occurs most even though some items cost less, ... > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL or programming?
    ... , min (Cost) as Cost ... join TableB as b on b.Item = x.Item ... Columnist, SQL Server Professional ... Item 1, $10, Category1 ...
    (microsoft.public.sqlserver.programming)