Q&A for anime and manga fans
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;