PIVOT

SQL Server PIVOT tutorial

Pivot can be a little tricky to use. I will explain with the help of example

Here is the output

Now lets pivot this table

Here is the output

Lets understand how the PIVOT Query works

In the first part you select a query that you want to pivot. In this example, my query is

so this is the data that pivot would be applied on. If you omit some fields in select query, pivot data will change accordingly.

In the next part, you use an aggregate function, typically SUM() or COUNT(). In my example it is

It is pretty clear from the expression itself. The first select query will be pivoted on “score in each subject”. The values in IN part is actually the column data that appears in the subject column. The data is moved into header fields. In this case we have select only Biology, Chemistry, Physica and Math. If there were more subjects in the Subject column, they will not be shown in the PIVOT table, because we are not displaying them.

Dynamic PIVOT Query

There is a way to select the subject values dynamically. The trick is to read the data first and store those values in a string and write a query and plug that string in.

Note that I am using distinct when pulling subject names otherwise, the same subject will be pulled twice and will result in error.