3 Ways to Pivot Data for Tableau

To read this blog in Spanish see 3 Formas de Pivotar Datos para Tableau

In the course of my work with Tableau, I’ve come to realize how much the platform loves for data to be nicely organized into rows. For example, let’s say you have some survey data which contains a single row for each response, then numeric answers from 1-5 for 5 questions. Typically survey results will list each of those answers in separate columns, like this:

But, data like this can often be very difficult to visualize in Tableau. It would be much better if your data were “pivoted” to look like this:

This will give you a single measure, which can make your data much easier to work with in Tableau. You can manually reorganize this data if you like, but that’s a lot of work. The good news is that there are a number of ways you can pivot your data automatically in preparation for visualization in Tableau. This blog will share 3 of those methods with you.

Pivoting with Tableau

The simplest method is to pivot data right in Tableau. If you bring your unpivoted data into Tableau, you can select the columns you wish to pivot—in our case, the five questions—then right-click and select “Pivot” as shown below. You can then rename the pivot fields and values as desired.

Pretty great, huh!! That is, until you try to do this with a data source other than Excel, Google Sheets, or text. Unfortunately, Tableau does not allow you to pivot connections to “real” databases such as SQL Server and Oracle, so you’re out of luck…or are you?

Pivoting with Tableau Prep

In case you haven’t heard, Tableau has just released a new software product called Tableau Prep (previously known as Project Maestro). The software is designed to allow you to quickly and easily “prep” your data before visualizing it in Tableau. While you can already do some of this data prep work in Tableau Desktop–unioning tables, pivoting columns to rows, grouping similar text, etc.–Tableau Prep allows you to take those manual steps and turn them into a single workflow which packages together all of the steps so that they can easily be run again in the future, saving you the time of manually preparing your data.

Tableau Prep has a built-in pivot feature which allows you to quickly and easily pivot your data from any Tableau Prep supported data source, including SQL Server, Oracle, and many other database platforms. My friend, Tableau Zen Master, and theexpert on visualizing survey data in Tableau, Steve Wexler, recently wrote a fantastic and thorough blog on using Tableau Prep for survey data, including pivoting the data. I can’t do the subject as much justice as Steve already has, so I’m just going to refer you to his blog post, Getting Your Survey Data“Just So” Using Tableau Prep. Go read it right now, then come back to read about the third and final method for pivoting your data.

Pivoting with Custom SQL

If, for some reason, Tableau Prep is not available to you or you simply do not want to introduce another tool to your process, there is one final solution which will work with many major database platforms, including both SQL Server and Oracle.

Let’s say you have a table called SurveyResponses in your SQL or Oracle database that looks just like the survey sample shown earlier. Here’s the create statement in SQL:

CREATE TABLE dbo.SurveyResponses

(

     [Respondent] [nvarchar](50),

     [Question1] [int],

     [Question2] [int],

     [Question3] [int],

     [Question4] [int],

     [Question5] [int],

)

Both SQL and Oracle have functions for doing this. The SQL/Oracle function PIVOT actually does the exact opposite of what we want—it changes rows into columns. So, what we want is UNPIVOT. UNPIVOT does pretty much the exact same thing as Tableau’s pivot function.

Let’s start by looking at a simple SELECT statement. To pull the data from the SurveyResponsestable, we’d write something like this. Note: I’m using SQL Server, but pretty much all of this translates to Oracle. When there are differences, I’ll point them out.

SELECT Respondent, Question1, Question2, Question3, Question4, Question5 FROM dbo.SurveyResponses

This will return the following data, which looks pretty much just like our original spreadsheet.

Unpivoting the data, however, is a bit more complex. Here’s the SQL

SELECT Respondent, Question, Answer

FROM

(

SELECT Respondent, Question1, Question2, Question3, Question4, Question5 FROM dbo.SurveyResponses

) AS source_query

UNPIVOT

(

Answer FOR Question IN (Question1, Question2, Question3, Question4, Question5)

) AS pivot_result

Let’s break this down in a bit more detail. First of all, we see our original select statement near the top, starting on the fourth line:

SELECT Respondent, Question1, Question2, Question3, Question4, Question5 FROM dbo.SurveyResponses

This will pull our initial data set directly from the table. Notice that this sub-query is then aliased as “source_query”. You can alias the sub-query with whatever name you choose, but it needs to be there.

Next we see the following:

UNPIVOT

(

Answer FOR Question IN (Question1, Question2, Question3, Question4, Question5)

) AS pivot_result

This will start the instruction to unpivot the previous select. It will take the value in each of the five question fields and put them into a single column called Answer. The name of each question (Question1, Question2, etc.) will be placed into a column called Question. Again, like the original select, we need to give this sub-query an alias; in this case, I’ve used “pivot_result”.

Finally, we have the very first part of the query, “SELECT Respondent, Question, Answer FROM”. This defines which fields are returned from the combination of the first two sub-queries.

When run it, we get the following result, which is exactly what we need:

The syntax for Oracle is almost exactly the same with one key difference. In Oracle, the sub-query aliases are not required. So your statement would look like this:

SELECT Respondent, Question, Answer

FROM

(

SELECT Respondent, Question1, Question2, Question3, Question4, Question5 FROM dbo.SurveyResponses

)

UNPIVOT

(

Answer FOR Question IN (Question1, Question2, Question3, Question4, Question5)

)

Now that we’ve written our SQL, we can leverage it in Tableau by using the following steps:

1)  Connect to your database in Tableau.

2)  Create a “New Custom SQL Query”

3)  Copy and paste the SQL documented above.

With Tableau Prep, custom SQL for pivoting data is likely a very niche solution, but it could come in handy in some use cases, so if you need it, you now know how.

Thanks for reading!!

Ken Flerlage, June 3, 2018

Hire Us! | Twitter | LinkedIn | GitHub | Tableau Public