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

 

30 comments:

  1. Can we transpose rows to columns..i.e. converting flat file/vertical data source to horizontal. My current data source is vertical which means I have all the KPIs in the same column and next column is month with last column as the data value. However, some of the KPIs are finished values (i.e. % or avg) and it becomes a challenge to calculate the numerator & denominator values so that the KPI can be reported when rolled up for multiple markets. Which is why if every KPI had a column of its own (horizontal structure) with each having data in it then it would have been best - Is it possible in Tableau?

    ReplyDelete
    Replies
    1. There are some ways to do it in Tableau using calculated fields, but the best way to do it would be using Tableau Prep. See https://www.tableau.com/about/blog/2019/1/new-tableau-prep-pivot-rows-columns-and-add-descriptions-any-change-99937

      Delete
  2. Ken - Thanks for posting the SQL portion. I tried it in Oracle and worked well. I only had to delete the "as" keyword because it was causing an error.

    ReplyDelete
  3. Hi Ken,

    If I need to assign labels to question 1 responses using case statement. means for 1-strong,2-Middel,... so on. After pivoting data.
    can you please help me on this.

    ReplyDelete
    Replies
    1. Difficult to help here. Any chance you could send me an email? flerlagekr@gmail.com

      Delete
  4. If we need to add new coloumn in data source; how can we pivot the newly added coloumns.
    For example in my data there are unit;local currency for last 5 years and pivotted it. Then I added $ coloumn to data source(excel). However I cannot see data with $ in tableau

    ReplyDelete
    Replies
    1. When you refresh the data source, you should see that new column come in. You can then right-click on the new field (in the Data Source tab) and choose "Add Data to Pivot".

      Delete
  5. Hello can I do pivot when I have answers but other fields. Trying to do this, but it is not working.

    ReplyDelete
    Replies
    1. Not exactly sure what you're asking. Can you clarify the question?

      Delete
  6. Thanks for posting this! Any thoughts why I might be getting the below error message:

    [Cloudera][ImpalaODBC] (360) Syntax error occurred during query execution: [HY000] : AnalysisException: Syntax error in line 6: UNPIVOT ^ Encountered: IDENTIFIER Expected: CROSS, FROM, FULL, GROUP, HAVING, INNER, JOIN, LEFT, LIMIT, OFFSET, ON, ORDER, RIGHT, STRAIGHT_JOIN, TABLESAMPLE, UNION, USING, WHERE, COMMA CAUSED BY: Exception: Syntax error
    Initial SQL Error. Check that the syntax is correct and that you have access privileges to the requested database.

    ReplyDelete
    Replies
    1. I don't think Impala supports UNPIVOT, unfortunately, but here are some alternatives that might work for you: https://stackoverflow.com/questions/41965421/impala-transform-columns-to-rows-without-using-union-all

      Delete
  7. I have calculated fields in data extract, can I pivot them in data extract?

    ReplyDelete
    Replies
    1. Not within Tableau Desktop, but you can pivot them in Prep if you use that extract as a source.

      Delete
  8. Can you use a custom SQL query on a sas7bdat file data source? I don't see the "New Custom SQL Query" button. Or is there another way to pivot? (I know it would be easy to just do a transpose in SAS but I'm hoping to figure out how to use Tableau)

    ReplyDelete
    Replies
    1. I just saw your post on the forums and responded. I don't use SAS so I'm not 100% sure, but based on other forums posts, it appears that custom SQL isn't available in SAS. Is Tableau Prep an option?

      Delete
  9. Hi Ken,
    Thanks for the info. I have found that once the data is pivoted in Tableau, it doesn’t connect to any other file that isn’t gone through pivoting. Of course can use tableau prep to do the same as well but I wonder if there is any way to pivot the data in MS Excel. Excel’s pivoting options only got me more confused to be honest.

    ReplyDelete
    Replies
    1. I don't understand what you mean. Would you be able to send me an email and potentially share an example? flerlagekr@gmail.com

      Delete
  10. Thanks Ken. Once Pivoted, would it be possible to create filters for individual questions for e.g. to count how many people answered (4) for Q1 but (1) for Q2 ?

    ReplyDelete
    Replies
    1. Yes, though it would be tricky using the pivoted data since those questions are now on separate rows. One approach I actually like for this is to keep both pivoted data and unpivoted data. So you'd create one "logical" table (assuming you're using 2020.2 or later), pivot the data there, then you'd drag your table over again to create a second logical table, which you'd leave unpivoted. You'd relate them based on the respondent, which will give you the questions in both rows and in columns, which would make filtering easier.

      Delete
  11. Using pivot in Tableau is repeating number of rows. How to ignore these new rows to get correct calculation results?
    Please help

    ReplyDelete
    Replies
    1. Hard to answer without more information. You might want to try LODs: https://www.flerlagetwins.com/2020/02/lod-uses.html. Otherwise, feel free to email me. flerlagekr@gmail.com

      Delete
  12. 2022, 3 AM at night here. Pivoting my whole dataset was massive for my analysis: since we can't select Measured Values/Names as a filter applicable to all worksheets, reducing the dimensions of the dataset with a pivot made it soooo much easier. Filters are cake now. Thank you!

    ReplyDelete
  13. Is it possible to pivot on more than one variable? For example, if there Questions 1 to 5 in columns and separate columns for subject (math, physics, english). Could these be pivoted into a single subject column?

    ReplyDelete
    Replies
    1. There is a hack that allows you to do it in Desktop. See https://www.flerlagetwins.com/2020/08/multiple-pivots.html. But, in this case, you'd be much better off doing the pivots in Tableau Prep as it's designed to do that kind of thing.

      Delete
    2. Thanks Ken! You and your brother's content is an amazing resource.

      Delete
  14. Hi there, I am not seeing the pivot option for my custom sql in 2021.3, after selecting multiple fields in the data source and then right clicking. Thoughts on how I might be doing this wrong?

    Thanks,
    Ryan

    ReplyDelete
    Replies
    1. If you're using custom SQL, then I'm assuming you're connected to a database. In that case, Desktop's pivot option is not available. You'll need to use one of the other two methods.

      Delete

Powered by Blogger.