Was this page helpful?
Yes No
Have a comment? Please leave it here. Thanks for your feedback!
All Tableau Help > Tableau Desktop and Web Authoring Help > 
Applies to: Tableau Desktop

Pivot Data from Columns to Rows

Sometimes, analyzing data that is stored in a crosstab format can be difficult in Tableau. When working with Microsoft Excel, text file, Google Sheets, and .pdf data sources, you can pivot your data from crosstab format into columnar format.

For example, suppose you have the number of devices sold by quarter for three vendors in three separate fields. You can pivot your data so that the vendor is in one field and the number of devices sold is in another field.

Pivot the data

After you have set up the data source, in the grid, select two or more columns. Click the drop-down arrow next to the column name, and then select Pivot. New columns called "Pivot field names" and "Pivot field values" are created and added to the data source. The new columns replace the original columns that you selected to create the pivot.

Add to the pivot

To add more data to the pivot, select another column, click the drop-down arrow next to the column name, and then select Add Data to Pivot. Make sure that the pivot columns and values look as expected before you begin your analysis.

 

To remove a pivot, click the drop-down arrow next to the name of a pivot column, and then select Remove Pivot.

Troubleshooting pivots

Pivot using custom SQL

You can also use custom SQL to pivot your data. When you use the UNION ALL operator in a custom SQL query, you can take values from distinct columns and put them into a new column.

For example, suppose you have a table called Contest.

Contest

Runner Start Time End Time
Amanda 9/3/2016 3:04 PM 9/3/2016 3:25 PM
Oscar 9/3/2016 3:04 PM 9/3/2016 3:21 PM
William 9/3/2016 3:04 PM 9/3/2016 3:16 PM

To optimize your analysis of this data in Tableau, you can use the following custom SQL query to pivot the "Start Time" and "End Time" columns so that their values are in a single column.

Select [Runner]
, 'Start' as [Action]
, [Start Time] as [Time]
From [Contest]
Union ALL
Select [Runner]
, 'End' as [Action]
, [End Time] as [Time]
From [Contest]

The above custom SQL query does the following:

The following table shows the results of this custom SQL query.

Runner Action Time
Amanda

Start

9/3/2016 3:04 PM
Oscar Start 9/3/2016 3:04 PM
William Start 9/3/2016 3:04 PM
Amanda End 9/3/2016 3:25 PM
Oscar End 9/3/2016 3:21 PM
William End 9/3/2016 3:16 PM

 

To pivot data using custom SQL

  1. Connect to your data.

  2. Double-click the New Custom SQL option in the left pane. For more information, see Connect to a Custom SQL Query.

  3. In the Edit Custom SQL dialog box, copy and paste the following custom SQL query and replace the contents with information about your table:

    Select [Static Column]
    , 'New Value (from Column Header 1)' as [New Column Header]
    , [Pivot Column Values 1] as [New Values]
    From [Table]
    Union ALL
    Select [Static Column]
    , 'New Value (from Column Header 2' as [New Column Header]
    , [Pivot Column Values 2] as [New Values]
    From [Table]
    Union ALL
    Select [Static Column]
    , 'New Value (from Column Header 3' as [New Column Header]
    , [Pivot Column Values 3] as [New Values]
    From [Table]

    Where the following is true:

  4. Click OK.