Query Design Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Design Help

Author

Topic

bcollinson
Starting Member

9 Posts

Posted - 2002-02-20 : 12:26:10

Hi,

I have three tables, for example:-

Categories
id (int)
description (varchar)

Products
id (int)
description (varchar)

Product_Categories
id (int)
id_product (int)
id_categories (int)

1. I need to know which products are in which categories
2. I would like to list the categories as columns, and the products as rows, with a '1' if the product is in the category (else a '0').

... failing that, I was going to do a CSV list of the category descriptions after the product description, but I would prefer the other format.

Any suggestions ?

Many thanks in advance.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-20 : 13:10:05

This code


DECLARE @SQL Varchar(1000)
SELECT @SQL = 'SELECT P.id_product '
SELECT @SQL = @SQL + '
	,MAX(CASE WHEN C.id_categories = ' + LTRIM(ID) + ' THEN 1 ELSE 0 END) AS "' + Description + '"'
FROM Categories

SET @SQL = @SQL + CHAR(13) + 'FROM Product_Categories GROUP BY id_product'

PRINT @SQL

will produce A quey that looks like this:


SELECT P.id_product,
	MAX(CASE WHEN C.id_categories = 1 THEN 1 ELSE 0 END) AS CategoryDescription,
	...
	MAX(CASE WHEN C.id_categories = N THEN 1 ELSE 0 END) AS CategoryDescription
FROM Product_Categories GROUP BY id_product

You can either exec it dynamically (EXEC (@SQL)) or paste the print output where you want it.