Jeff Moden (3/30/2013)
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.
Good example of a dynamic cross tab, Vinu. Can you change it to put output columns in the same order as requested?
Yes Jeff,
I did manage to get the columns in the order requested by tweaking the code i posted earlier.
Here is the new code with the desired order of columns:
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) + ',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)
P.S.: Jeff, I'm gonna tell my dba friend's that Jeff Moden liked my solution.......ROFL!!!!!! :-D:-D