Pivot - SQL - values from SubQuery

I have a simple query like this..

USE AdventureWorks;
GO

SE开发者_C百科LECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture; 



DaysToManufacture  AverageCost  
0                  5.0885  
1                  223.88  
2                  359.1082  
4                  949.4105  

A simple pivot gives me

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;  

Gives me

Cost_Sorted_By_Production_Days   0                     1                     2                     3                     4

AverageCost                    5.0885                223.88                359.1082              NULL                  949.4105

But the values in pivot query are hardcode.. I want to get those values from a subquery..

select DaysToManufacture FROM Production.Product GROUP BY DaysToManufacture;

But pivot doesn't let me get values from subquery, Is there any way to do this other than writing a dynamically generated query?

No. This can only be done using a dynamic query. I would be really interested to find out as well if there is a way.

There are some examples which a quick Google search found using COALESCE to create the column list. However I prefer to create the list of columns using STUFF. However I did find this article about the use CTE's and dynamic pivots which may be of assitance as well

To get the values from subquery we can use dynamic query. I have done some research and found a solution.

DECLARE @query VARCHAR(4000)
DECLARE @days VARCHAR(2000)
SELECT  @days = STUFF(( SELECT DISTINCT
                        '],[' + ltrim(str(DaysToManufacture))
                        FROM    Product
                        ORDER BY '],[' + ltrim(str(DaysToManufacture))
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @query =
'SELECT ''AverageCost'' AS Cost_Sorted_By_Production_Days,'   +
@days + 
'FROM  (SELECT DaysToManufacture, StandardCost FROM Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN (' + @days +  ')) AS PivotTable;'  

EXECUTE (@query)

Thanks to .NET Technologies