T-SQL Pivot vs. Pivot tables vs. GROUP BY queries Summary Transformation Usage Advantages Disadvantages Can have many different aggregated used to provide calculations based GROUP BY will not solve all values for any row (or dimension). Good to GROUP BY() on groups (uses the GROUP BY aggregation needs. A higher level build result sets for reports & query) of aggregation may be needed. spreadsheets Total, Sum, Count, Min, Max, ?? - Compacts a ton of data down into a small Harder to parameterize. Typically PIVOT() used to aggregate large amounts footprint table, which is the perfect source can only have one aggregate of data into a crosstab format for a column or line chart. Feature GROUP BY Query PIVOT Query Group BY queries can provide many columns of aggregated results such as counts, Pivot queries provide a totals, averages. Moving averages, ranks, etc.) The aggregations can be calculated static compacted table of for different grouping levels, where the chosen grouping outputs one row of results displaying ONE aggregated data per grouping of dimension attributes (such as geographic region, aggregated value in a product line, employee, project, etc.). tabular format. The aggregation for the ONE USE If you have one level of grouping (say country) then the totals will be at a high level calculated value is based on (CEO level). This minimal grouping may be visualized on an executive’s dashboard. two dimensions. using If you have 2 or 3 levels of grouping then you have more rows of more detailed For example production data (i.e., within each country you have customers grouped within cities, where one units can be shown for record per customer is calculated and provided in the dataset. month and machine
T-SQL Pivot vs. Pivot tables vs. GROUP BY queries Summary Transformation Usage Advantages Disadvantages GROUP BY() used to provide calculations based on groups (uses the GROUP BY query) Can have many different aggregated values for any row (or dimension). Good to build result sets for reports & spreadsheets GROUP BY will not solve all aggregation needs. A higher level of aggregation may be needed. PIVOT() Total, Sum, Count, Min, Max, ?? - used to aggregate large amounts of data into a crosstab format Compacts a ton of data down into a small footprint table, which is the perfect source for a column or line chart. Harder to parameterize. Typically can only have one aggregate Feature GROUP BY Query PIVOT Query USE Group BY queries can provide many columns of aggregated results such as counts, totals, averages. Moving averages, ranks, etc.) The aggregations can be calculated for different grouping levels, where the chosen grouping outputs one row of aggregated data per grouping of dimension attributes (such as geographic region, product line, employee, project, etc.). If you have one level of grouping (say country) then the totals will be at a high level (CEO level). This minimal grouping may be visualized on an executive’s dashboard. using If you have 2 or 3 levels of grouping then you have more rows of more detailed data (i.e., within each country you have customers grouped within cities, where one record per customer is calculated and provided in the dataset. Pivot queries provide a static compacted table of results displaying ONE aggregated value in a tabular format. The aggregation for the ONE calculated value is based on two dimensions. For example production units can be shown for month and machine
Featherman-TSQL-Intermediate-Pivot-Transformations.docx