3 Ways to Pivot Data for Tableau

• Read original article here

3 Formas de Pivotar Datos para 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.

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?

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.

theGetting Your Survey Data“Just So” Using Tableau Prep 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 expert 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.

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.

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.

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.

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.

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:

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.