Pivoting in SQL Server

Hello, welcome back to another post on LearningSQL with Beno.

Do you remember that in my last post I needed to use recursive CTEs in order to create a BOM? No, that’s okay, you can check it out here.

I am not sure if I mentioned that while doing that BOM, I needed to present it in a very specific way. Every sub product of every product should be as a column labeled with their respective product level. Like this:

Something like that. In the one I did, I had to include more columns like attributes of the sub products in subsequent levels.

ROWS TO COLUMNS

There have been situations where you need to move your rows into columns, like if you were doing a pivot table in excel. Transact – SQL has the PIVOT statement, which is like grouping and  aggregating data, but it transforms the rows into columns.

In any pivot query, you need to know three elements of it. What you want to see on rows, what you want in columns and a value, which will be at the intersection of each column and row, this will be the aggregation element.

I’m going to show you a very simple example of pivoting data. We’re going to use our TestDB. Suppose that you have the following query and result set.

It’s very easy, we want to know how many students by course, and we want to see the department the course belongs to. Your manager just asked about this query and you sent the data, but she replies back and tells you that she wants to see the Department as columns. 

Do not start running in circles, I got you covered. You’re going to learn how to do that very easily. The basic syntax for a PIVOT query is the following:

WITH PivotData
AS
(
    SELECT
        <what you want to see as rows>,
        <what you want to see in columns>,
        <the value you want to aggregate>
    FROM
     <source table>
)
SELECT <list of columns>
FROM
    PivotData
    PIVOT( <the aggregate function you want to use (SUM,MAX, AVG...etc)>(<the aggregate column>)
        FOR <what you want in columns> IN (<the different values of what you want in columns>) AS P;

I know it seems a little bit confusing, but I’m going to show you with actual data for you to understand it better.

This is a little tricky and somewhat annoying. The fact that you have to hard code the column values in there is not good at all. There is a way that you can dynamically create your pivot query, but I’ll leave that for another time.

COLUMNS TO ROWS

So, you already learned how to change rows to columns, but what happens if you need to do the opposite. Transact SQL has the UNPIVOT statement, which is basically the inverse process of what a PIVOT does.

You just uploaded a comma separated values file with company revenue data that looks like this:

See that the column names have the years, and in this case you need the years to be in the rows instead because, uhm… because you need it like that, period. The basic syntax is almost the same, look:

SELECT 
    < column list >, 
    < the name you want to give to the target column(s) >, 
    < values to unpivot >
FROM 
    < source table >
UNPIVOT
    ( 
        < values to unpivot > 
        FOR < name you want for target column > IN( <source columns> ) 
    ) AS U;

Let’s see an example with the data we just loaded:

As you can see, we now have the Company, Year and Revenue as columns. It still is kind of annoying that you have to hard code the names there, but as I’ve already mentioned there is a way to create dynamic pivot and unpivot queries.

That’s all for today guys, I hope you liked the post and that you’ve learned something new or refreshed some old knowledge.

Also, remember that you can find the code for this and other posts in the following link: https://github.com/learningsql/sql-code

If you have any questions or comments, shoot me an email, leave a message or tweet me here.

Don’t forget to like and share. See you soon. Cheers!