dynamic PIVOT

Last Post 20 Jan 2012 01:28 PM by darko_73. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
darko_73
New Member
New Member

--
20 Jan 2012 01:28 PM
Hi,
THis is example I found on msdn:

USE AdventureWorks ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;


-- Pivot query from the above
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

0            1          2              4
------- ------ --------- -------- ------- 
5.0885  223.88 359.1082  949.4105

Now the problem is when i try to use columns dynamically.  I am getting each column heading twice.
The same thing happen in my real problem.

--dynamic


DECLARE @cols NVARCHAR(2000)   
SELECT  @cols = COALESCE(@cols + ',[' + convert(varchar(10),DaysToManufacture) + ']',
                         '[' + convert(varchar(10),DaysToManufacture) + ']')
FROM    Production.Product
group by DaysToManufacture
--order BY DaysToManufacture
print @cols

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT  *, '+
@cols +'
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ( '+
@cols +' )
) AS PivotTable';
EXECUTE(@query)

0            1          2              4              0           1          2               4
------- ------ --------- -------- ------- ------ -------- --------- -----------   --------
5.0885  223.88 359.1082  949.4105 5.0885  223.88 359.1082 949.4105



Acceptable Use Policy
---