Consider a scenario you want to create a quick report from a table product in SQL showing total product sold over the last 12 months, in which each column represents the month, last column could be the YTD sales. There will be the horizontal sub-total for each category for example sale by products, regions etc.
This can be quickly done using Excel or other BI programs, but in case you want to do it right in the SQL Server, CTE, Pivot and Grouping sets can help to achieve this requirement. The sample of transactional sales data is shown below, this structure is alike in almost database design.
Now you are required to extract the sale of products in the structure below
With CTEs, Pivot and Grouping sets, the original product table can be turned into more understandable report. The SQL scripts for the above screnarios are as follows.
Dynamic Pivot queries
Consider a case if new products, let's say "Headphone", "Tablet" are added into the table Product, then the script in the first scenario would need to be manually adjusted. In other words, we need to insert these items in the list of column in select & pivot statement. In order to avoid modifying the query every time any new product is added, we can dynamically extract the product list, build a query statement, and execute it using the store procedure sp_executesql. The output of scenario 1 and the dynamic pivot query are now as follow.
Dynamic pivot query can also be useful if we need keep the pivot value columns (eReader, Headphone etc.) and change the grouping items e.g. City, Year or other criteria. In this case, we would need to create a store procedure accepting the parameterized values for grouping items.
No comments:
Post a Comment