February 6, 2020 at 12:00 am
Comments posted to this topic are about the item Introduction to PIVOT operator in SQL
February 6, 2020 at 3:40 pm
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]
February 6, 2020 at 3:50 pm
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-2-dynamic-cross-tabs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply