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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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