In this article, we will explore the concept of SQL Server PIVOT tables and how they can be used to transform data in a tabular format. PIVOT tables are a powerful tool that allows you to rotate rows into columns, making it easier to analyze and present data in a more meaningful way.

Data Model and General Idea

Before diving into the details of SQL Server PIVOT tables, let’s first understand the data model and the general idea behind it. In our example, we have four tables: call, call_outcome, customer, and city. Our goal is to create a report where each city will be in a separate row, and we’ll count the number of call outcomes related to each city.

To achieve this, we’ll need to use data from these four tables and establish the relationships between them. By examining the data in these tables, we can confirm that we have the necessary information to create the desired report.

Report Categories and Data

Next, we need to prepare the reporting categories. We want to have a combination of all cities and all possible call outcomes. To achieve this, we’ll use the CROSS JOIN operator, which will give us all possible combinations of cities and call outcomes.

Once we have the reporting categories, we can join all four tables to retrieve the necessary data for our report. This involves joining the call, call_outcome, customer, and city tables based on their respective foreign key relationships.

Report without SQL Server PIVOT Table

Now that we have the reporting categories and data, we can join them together to create the report. We’ll use subqueries and a LEFT JOIN to ensure that all categories are present in the final output, even if there is no data for a particular category.

SQL Server PIVOT Table (Static)

In the previous section, we created the report without using the SQL Server PIVOT operator. Now, let’s explore how we can achieve the same result using the PIVOT operator. The static PIVOT allows us to specify the columns we want in the final report.

We’ll define the aggregate function we want to apply, which in our case is COUNT(call_duration). We’ll also specify the columns we want in the final report by listing all the values we want as columns. This approach works well if the values in the outcome_text column do not change frequently.

SQL Server PIVOT Table (Dynamic)

In some cases, the values in the outcome_text column may change frequently, and we don’t want to modify the query every time a new value is added or removed. In such situations, we can use dynamic SQL to create a SQL Server PIVOT table query with an unknown number of columns.

Dynamic SQL allows us to build queries as strings and execute them using the sp_executesql system procedure. We can dynamically retrieve the column names from the outcome_text column and incorporate them into our query. This approach ensures that our query will work even if the values in the outcome_text column change.

Conclusion

SQL Server PIVOT tables are a powerful tool for transforming data and presenting it in a more meaningful way. They allow you to rotate rows into columns, making it easier to analyze and interpret data. By using the static or dynamic PIVOT approach, you can create reports that adapt to changes in the underlying data.

It is important to understand the data model and relationships between tables before using the PIVOT operator. Additionally, dynamic SQL can be a useful technique when dealing with changing data values. I encourage you to explore and experiment with SQL Server PIVOT tables to gain a deeper understanding of their capabilities.