Dynamic column count
-
I have the following tables -
Products - ProductID, ProdDecsription
ProductCategory - ProductID, CategoryID
Category - CategoryID, CatDescription
One product can be in a number of categories (there are currently only 7 categories and it's not likely to grow much).
My problem is to create an output like this -
ProductID, ProdDescription, Category 1, Category 2, Category 3, etc
148 Clear plastic drum 125mm dia x 35mm high 0 0 0 0 0 1 0
149 Vienna Pastel Sugared Almonds - Bulk 0 1 1 0 1 0 0
150 Peewit eggs - bulk (sometimes) 0 0 0 0 1 0 0
Currently I have the following code -
ALTER PROCEDURE spo_dynamicColumn
AS
DECLARE @i int
DECLARE @t int
DECLARE @strSQL varchar(8000)
CREATE TABLE ##ProductCategory
(
ProductID int,
ProductName varchar(80),
CategoryID int
)
INSERT INTO ##ProductCategory
SELECT TOP 100 PERCENT dbo.Products.ProductID, dbo.Products.ProductName, dbo.ProductCategory.CategoryID
FROM dbo.Products INNER JOIN
dbo.ProductCategory ON dbo.Products.ProductID = dbo.ProductCategory.ProductID INNER JOIN
dbo.CategoryProducts ON dbo.ProductCategory.CategoryID = dbo.CategoryProducts.CategoryProductID
WHERE (dbo.Products.Deleted = 0) AND (dbo.CategoryProducts.Deleted = 0)
ORDER BY dbo.Products.ProductID
SELECT @i = 1
SET @t = (SELECT COUNT(*) FROM dbo.CategoryProducts)
SELECT @strSQL = 'SELECT ProductID, ProductName, '
WHILE @i <= @t
BEGIN
SELECT @strSQL = @strSQL +
'(CASE CategoryID WHEN ' +
CONVERT(nvarchar,@i) +
' THEN 1 ELSE 0 END) AS "' +
CONVERT(nvarchar,@i) + '"'
IF @i < @t
SELECT @strSQL = @strSQL + ','
SELECT @i = @i +1
END
SELECT @strSQL = @strSQL +
' FROM ##ProductCategory'
EXEC (@strSQL)
DROP TABLE ##ProductCategory
RETURN
Which gives me this result -
148 Clear plastic drum 125mm dia x 35mm high 0 0 0 0 0 1 0
149 Vienna Pastel Sugared Almonds - Bulk 0 0 1 0 0 0 0
149 Vienna Pastel Sugared Almonds - Bulk 0 1 0 0 0 0 0
149 Vienna Pastel Sugared Almonds - Bulk 0 0 0 0 1 0 0
150 Peewit eggs - bulk (sometimes) 0 0 0 0 1 0 0
It's close, but I need to consolidate all the details for product 149, onto 1 line.
Any ides would be much appreciated.
Tim -
Try adding grouping like this
SELECT @strSQL = 'SELECT ProductID, ProductName, '
WHILE @i <= @t
BEGIN
SET @strSQL = @strSQL +
'MAX(CASE CategoryID WHEN ' +
CONVERT(nvarchar,@i) +
' THEN 1 ELSE 0 END) AS [' +
CONVERT(nvarchar,@i) + ']'
IF @i < @t
SET @strSQL = @strSQL + ','
SET @i = @i +1
END
SET @strSQL = @strSQL +
' FROM ##ProductCategory GROUP BY ProductID, ProductName'
Far away is close at hand in the images of elsewhere.
Anon. -
David,
Thankyou very much, it works perfectly.
Tim
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply