Hi
A good article for crosstabs is http://www.sqlservercentral.com/articles/T-SQL/63681/
Here's an example that uses a crosstab allowing for up to 5 different product pricings
-- Set up some test data
;with product as (
SELECT *
FROM (VALUES
(1, 'Beer', 1)
,(2, 'Wine', 1)
,(3, 'Bourbon', 1)
,(4, 'Crisps', 2)
,(5, 'Nuts', 2)
) AS product(ProductId, ProductName,CategoryID)
)
,category as (
SELECT *
FROM (VALUES
(1,'Beverages')
,(2,'Snacks')
) AS product(CategoryID, CategoryName)
)
,productprices as (
SELECT *
FROM (VALUES
(1,1,2.00)
,(1,12,20.00)
,(1,24,36.00)
,(2,1,12.00)
,(2,6,60.00)
,(3,1,45.00)
,(4,10,12.00)
,(5,5,10.00)
,(5,10,16.00)
) AS product(ProductID, ProductQuantity, ProductCost)
)
-- Create query to pivot data
SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName
,MAX(CASE WHEN pp.Seq = 1 THEN pp.ProductQuantity ELSE null END) ProductQuantity1
,MAX(CASE WHEN pp.Seq = 1 THEN pp.ProductCost ELSE null END) ProductCost1
,MAX(CASE WHEN pp.Seq = 2 THEN pp.ProductQuantity ELSE null END) ProductQuantity2
,MAX(CASE WHEN pp.Seq = 2 THEN pp.ProductCost ELSE null END) ProductCost2
,MAX(CASE WHEN pp.Seq = 3 THEN pp.ProductQuantity ELSE null END) ProductQuantity3
,MAX(CASE WHEN pp.Seq = 3 THEN pp.ProductCost ELSE null END) ProductCost3
,MAX(CASE WHEN pp.Seq = 4 THEN pp.ProductQuantity ELSE null END) ProductQuantity4
,MAX(CASE WHEN pp.Seq = 4 THEN pp.ProductCost ELSE null END) ProductCost4
,MAX(CASE WHEN pp.Seq = 5 THEN pp.ProductQuantity ELSE null END) ProductQuantity5
,MAX(CASE WHEN pp.Seq = 5 THEN pp.ProductCost ELSE null END) ProductCost5
FROM Product p
INNER JOIN Category c ON p.CategoryID = c.CategoryID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost
FROM ProductPrices
) pp ON p.ProductId = pp.ProductID
GROUP BY p.ProductID, p.ProductName, c.CategoryID, c.CategoryName