locked
Select Query on Pivoted Columns - Is it possible? RRS feed

  • Question

  • Hi Everyone,

    Here's a sample Query for a simple pivot I got from technet microsoft:

    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;

    As you can see they used ([0],[1],[2],[3],[4]) as pivoted columns.

    Is there a way I can change that pivoted columns to a SELECT Query like this:

    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 (SELECT DayCount FROM Production.Days)
    ) AS PivotTable;

    I cannot encode the pivoted columns manually because it is always changing.

    Please help!

    TIA!

    Thursday, October 13, 2016 10:59 AM

Answers

  • No, cause the pivot column list must be a literal, thus static. The reason - in short - it makes otherwise no sense in a relational database.

    You may use dynamic SQL for that, but be warned and read Erland's excellent article about it first: The Curse and Blessings of Dynamic SQL.

    DECLARE @Sql NVARCHAR(MAX) = N'
    	SELECT  ''AverageCost'' AS Cost_Sorted_By_Production_Days ,
    			@Columns
    	FROM    ( SELECT    DaysToManufacture ,
    						StandardCost
    			  FROM      Production.Product
    			) AS SourceTable 
    	PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ( @Columns ) ) AS P;
    ';
    
    DECLARE @Colunms NVARCHAR(MAX) = STUFF((	SELECT   ', ' + QUOTENAME(CAST(DayCount AS NVARCHAR(255)))
    										   FROM     Production.Days
    										   FOR XML PATH(''), TYPE
    								 ).value('.', 'NVARCHAR(MAX)'), 1, 2);
    
    SET @Sql = REPLACE(@Sql, '@Colunms', @Colunms);
    
    PRINT @Sql;
    
    EXECUTE (@Sql);
    

    Thursday, October 13, 2016 11:23 AM