SQL Server Pivot Tables with Dynamic Functionality

Pivot tables are a powerful tool in SQL Server that allows users to transform data from rows into columns, making it easier to analyze and summarize large datasets. With the dynamic functionality of pivot tables, you can create more complex queries that can handle changing data structures, making it easier to work with a variety of data sources.

Dynamic Pivot Tables

Pivot tables are a way to organize data, but usually you have to set up the columns ahead of time. Dynamic pivot tables are different because they can create the columns for you based on the data. This makes them better for dealing with data that changes a lot.

Applying the PIVOT Operator

We can make a pivot table in SQL Server using our data. We'll keep the Student column as rows and use Subject for columns. We need to list the distinct column values we want to see in the pivot table when writing the SQL query. In this case, we have three distinct subjects, so we need to include them in the list when creating the pivot table.

As you can see in the figure above, the pivot table has been created and we have converted the rows for?SubjectNames?into distinct columns.

Now let us try to break the above script and understand how it works. If you see the script, clearly, we can divide it into two separate sections – the first part in which we select data from the original table as it is and in the second part, we define how the pivot table should be created. In the script, we also mention some specific keywords like?SUM,?FOR?and?IN,?which are meant for use by the?PIVOT?operator only. Let’s quickly talk about these keywords.

The SUM operator

I used a tool called the SUM operator in my script. It adds up all the numbers in the "Mark" column so we can use them in a special table. This table needs a column with added up values to show them in the correct way.

The FOR keyword

The FOR keyword is a special keyword used for the pivot table in SQL Server scripts. This operator tells the pivot operator on which column do we need to apply the pivot function. Basically, the column which is to be converted from rows into columns.

The IN keyword

The IN keyword, as already explained above, lists all the distinct values from the pivot column that we want to add to the pivot table column list. For this example, since we have only three distinct values for the?SubjectNames?column, we provide all the three in the list for the?IN?keyword.

Building a Dynamic Stored Procedure for PIVOT Tables

We can put the entire PIVOT script into a stored procedure. This will allow us to customize our requirements by changing some parameter values. The SQL script for the dynamic PIVOT table is provided below.

As you can see in the script above, I have two parameterized variables. The details of these two parameters are as follows.

• @ColumnToPivot?– This parameter accepts the name of the column in the base table on which the pivot table is going to be applied. For the current scenario, it will be the “SubjectName” column because we would like to pivot the base table and display all the subjects in the columns
• @ListToPivot?– This parameter accepts the list of values that we want to visualize as a column in the pivot table in SQL

Executing

Now that our dynamic stored procedure is ready let us go ahead and execute it. Let us replicate the first scenario where we visualized all the three subjects – Computer Science, Math and Chemistry in the pivot table in SQL. Execute the script as below.

As easy as that, you can add as many columns you’d like to add to the list and the pivot table will be displayed accordingly.

Now, let's think about a different situation where we want to show the names of students in the columns and the subject names in the rows - the opposite of what we did before. The solution is straightforward, as you might have guessed. We just need to change the values in both parameters. The first parameter should be "Name" and the second should be the list of student names we want to display in the columns. Here's the stored procedure:

As shown in the image above, the SQL pivot table is changed dynamically without having to alter the code that powers it.

This article covered the basics of creating pivot tables in SQL and provided an example of how to implement them. Additionally, I explained how to parameterize the pivot table script so that the table structure can be easily modified without changing the underlying code.