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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy