Introduction to PIVOT operator in SQL

  • Gouri Shankar

    SSC-Addicted

    Points: 413

    Comments posted to this topic are about the item Introduction to PIVOT operator in SQL

  • pamela.j.cihak

    Newbie

    Points: 2

    Is there a way to specify a column name for the IN section of code to keep it dynamic? Otherwise, every time there is a new value in the column, it will need to be added to the list of values for the IN section.

    Example:

    SELECT * FROM ( SELECT [dd].[CalendarYear] AS [OrderYear] ,[dst].[SalesTerritoryGroup] AS [SalesTerritoryGroup] ,SUM(SalesAmount) [SalesAmount] FROM [dbo].[FactInternetSales] fs INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey INNER JOIN [dbo].[DimSalesTerritory] dst ON dst.SalesTerritoryKey = fs.SalesTerritoryKey GROUP BY [dd].[CalendarYear] ,[dst].[SalesTerritoryGroup] ) AS [SalesByTerritoryAndYear] PIVOT ( SUM([SalesAmount]) FOR [SalesTerritoryGroup] IN ( <columnname> ) ) AS [PivotSalesByTerritoryAndYear]

  • DesNorton

    SSC-Insane

    Points: 22967

    pamela.j.cihak wrote:

    Is there a way to specify a column name for the IN section of code to keep it dynamic? Otherwise, every time there is a new value in the column, it will need to be added to the list of values for the IN section.

    Example:

    SELECT * FROM ( SELECT [dd].[CalendarYear] AS [OrderYear] ,[dst].[SalesTerritoryGroup] AS [SalesTerritoryGroup] ,SUM(SalesAmount) [SalesAmount] FROM [dbo].[FactInternetSales] fs INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey INNER JOIN [dbo].[DimSalesTerritory] dst ON dst.SalesTerritoryKey = fs.SalesTerritoryKey GROUP BY [dd].[CalendarYear] ,[dst].[SalesTerritoryGroup] ) AS [SalesByTerritoryAndYear] PIVOT ( SUM([SalesAmount]) FOR [SalesTerritoryGroup] IN ( <columnname> ) ) AS [PivotSalesByTerritoryAndYear]

     

    Take a look at these 2 articles on dynamic pivoting and cross-tabs

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply