vinu512 (3/28/2013)

---

This is how you can transform Mickey's query into a Dynamic Cross tab:

Declare @sql Varchar(MAX)

-- Set up some test data

Create table product(ProductId Int, ProductName Varchar(30),CategoryID Int)

Create table category(CategoryID Int, CategoryName Varchar(30))

Create table productprices(ProductID Int, ProductQuantity Int, ProductCost Float)

Insert Into product

Select1, 'Beer', 1

Union ALL

Select 2, 'Wine', 1

Union ALL

Select 3, 'Bourbon', 1

Union ALL

Select 4, 'Crisps', 2

Union ALL

Select 5, 'Nuts', 2

Insert Into category

Select1,'Beverages'

Union ALL

Select 2,'Snacks'

Insert Into productprices

Select1,1,2.00

Union ALL

Select 1,12,20.00

Union ALL

Select 1,24,36.00

Union ALL

Select 2,1,12.00

Union ALL

Select 2,6,60.00

Union ALL

Select 3,1,45.00

Union ALL

Select 4,10,12.00

Union ALL

Select 5,5,10.00

Union ALL

Select 5,10,16.00

-- Create query to dynamically pivot data

Select @sql = 'SELECT p.ProductID, p.ProductName, c.CategoryID, c.CategoryName, '

Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductQuantity ELSE null END) As ProductQuantity' + Cast(pp.Seq As Varchar)

FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost

FROM productprices

) pp FOR XML PATH('')),1,1,'') + ', '

Select @sql = @sql + STUFF((Select DISTINCT ',MAX(CASE WHEN pp.Seq = ' + Cast(pp.Seq As Varchar) + ' THEN pp.ProductCost ELSE null END) As ProductCost' + Cast(pp.Seq As Varchar)

FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductQuantity) Seq, ProductID, ProductQuantity, ProductCost

FROM productprices

) pp FOR XML PATH('')),1,1,'')

Select @sql = @sql + ' 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 '

Execute(@sql)

Hope this helps.