This site is still under construction. However, I wanted to get my PIVOT information posted before the website has been fully configured.
Creativity comes in many forms. Could be a unique query solution or a beautiful crocheted blanket. Whatever the case may be, I will do my best to post my various creative projects.
PIVOT syntax is awesome. Dynamic PIVOT is awesomer-er. Turn data into information by aggregating data while rotating rows into columns. Each of the three ways to pivot (manual, PIVOT operator, and dynamic PIVOT) will be covered.
It is helpful if attendees have a basic knowledge of the pivot concepts.
Technical Definition
https://searchsqlserver.techtarget.com/definition/pivot-table
A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or database table to obtain a desired report. The tool does not actually change the spreadsheet or database itself, it simply “pivots” or turns the data to view it from different perspectives.
Pivot tables are especially useful with large amounts of data that would be time-consuming to calculate by hand. A few data processing functions a pivot table can perform include identifying sums, averages, ranges or outliers. The table then arranges this information in a simple, meaningful layout that draws attention to key values.
When users create a pivot table, there are four main components: Columns- When a field is chosen for the column area, only the unique values of the field are listed across the top. Rows- When a field is chosen for the row area, it populates as the first column. Similar to the columns, all row labels are the unique values and duplicates are removed. Values- Each value is kept in a pivot table cell and display the summarized information. The most common values are sum, average, minimum and maximum. Filters- Filters apply a calculation or restriction to the entire table.
For example, a store owner might list monthly sales totals for a large number of merchandise items in an Excel spreadsheet. If they wanted to know which items sold better in a particular financial quarter, they could use a pivot table. The sales quarters would be listed across the top as column labels and the products would be listed in the first column as rows. The values in the worksheet would show the sum of sales for each product in each quarter. A filter could then be applied to only show specific quarters, specific products or averages.
A pivot table helps users answer business questions with minimal effort. Common pivot table uses include: To calculate sums or averages in business situations. For example, counting sales by department or region. To show totals as a percentage of a whole. For example, comparing sales for a specific product to total sales. To generate a list of unique values. For example, showing which states or countries have ordered a product. To create a 2x2 table summary of a complex report. To identify the maximum and minimum values of a dataset. To query information directly from an online analytical processing (OLAP) server.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements.
The SQL Server (Transact-SQL) PIVOT clause allows you to write a cross-tabulation. This means that you can aggregate your results and rotate rows into columns.