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

Pivot Data from Columns to Rows

You can pivot the data in your Microsoft Excel, text file, Google Sheets, and .pdf data sources from crosstab format into columnar format to more easily perform analysis.

Or, if you are not working with the types of data listed, you can use custom SQL as an alternative way to pivot your data in Tableau.

Pivot Excel, text, .pdf and Google Sheets data

For example, suppose your data source contains sales values by year:

Region 2014 2015 2016
North 500 450 150
East 150 300 225
South 325 300 375
West 200 200 150
Central 300 200 250

The following image shows the example data source in the grid (left) and a view that you can create with the example data source (right).

Although you see a breakdown of sales by year, suppose you want to show all sales values by year, irrespective of region. To do this with the example data source, use pivot to change the format of the existing fields so that all year values are contained in one field, and all sales values are contained in another.

The following image shows the example data source in the grid after using pivot (left) and a view that you can create with the pivot fields (right).

To pivot fields

  1. On the data source page, in the grid, select two or more fields to pivot.

  2. Click the drop-down arrow next to the field name, and then select Pivot.

The original fields in the data source are replaced with new fields called “Pivot field names” and “Pivot field values.” You can always rename the new pivot fields. If you decide that using pivot does not help, you can undo the changes or remove the pivot. Alternatively, you can change the data type of the pivot field to adjust how the data is interpreted.

To add more fields to a pivot

  1. On the data source page, in the grid, click the drop-down arrow next to the field name.

  2. Select Add Data to Pivot.

To remove a pivot

  1. On the data source page, in the grid, click the drop-down arrow next to the field name.

  2. Select Remove Pivot. All new pivot fields are removed and the original fields are restored.

At a glance: Working with pivots

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.