0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

DECLARE @Products TABLE (ID INT, Name VARCHAR(50));
DECLARE @Categories AS TABLE (ID INT, Name VARCHAR(50));
DECLARE @ProductCategoryMatrix AS TABLE (ProductID INT, CategoryID INT);

INSERT INTO @Products (ID, Name) VALUES (1, 'Pen'), (2, 'Pad'), (3, 'Ball');
INSERT INTO @Categories VALUES (1, 'Office'), (2, 'Home'), (3, 'Work');
INSERT INTO @ProductCategoryMatrix VALUES (1, 1), (1, 2), (2, 1), (2, 2), (2, 3), (3, 3);

WITH CategoriesOrdered AS (
    SELECT p.ID AS ProductID
           ,'Cat' + CAST(ROW_NUMBER() OVER(PARTITION BY pc.ProductID ORDER BY pc.CategoryID) AS VARCHAR(1)) AS CategoryColumn
           ,pc.CategoryID
    FROM @Products p
    INNER JOIN @ProductCategoryMatrix pc
        ON pc.ProductID = p.ID
)
,CategoriesFlat AS (
    SELECT *
    FROM CategoriesOrdered
    PIVOT (MIN(CategoryID) FOR CategoryColumn IN ([Cat1], [Cat2], [Cat3], [Cat4], [Cat5])) AS pvt
)
,ProductsWithCategories AS (
    SELECT *
    FROM @Products p
    LEFT JOIN CategoriesFlat cf
        ON cf.ProductID = p.ID
)
SELECT *
FROM ProductsWithCategories;

fork query

edited oct 30 anonymous

created mar 15 12 Cade Roux