--- schema_version: "1" id: a27650ea53251349fa8206c2569820ca920d472b95470904de7ce55e34567835 title: Pivot Data from Columns to Rows collection: tableau version: current locale: en-gb portal_url: https://docs.salesforce.com/tableau/current/en-gb/public/desktop/pivot.md canonical_url: https://help.tableau.com/current/public/desktop/en-gb/pivot.htm source_site: help.tableau.com content_type: documentation content_hash: 40c2c8b58e9b6466a9b6af2919ac2a97363bc5e1685a5e146a63da224e3a35f2 description: Pivot data from crosstab columns to rows, including via custom SQL. --- [Skip to main content](#main-content) # Pivot Data from Columns to Rows Sometimes, analysing 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. If you are working with other data sources, you can [Pivot using custom SQL (Tableau Desktop)](#Pivot). For example, suppose you have the number of devices sold per 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. ![Content pivoted from columns to rows.](/tableau/current/en-gb/public/desktop/en-gb/Img/qs_pivot_example.png) ## 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. ![Context menu on a field with option to a Pivot field.](/tableau/current/en-gb/public/desktop/en-gb/Img/qs_pivot_command.png) ## 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. ![Context menu on a field with option to add data to pivot.](/tableau/current/en-gb/public/desktop/en-gb/Img/qs_pivot_add.png) To remove a pivot, click the drop-down arrow next to the name of a pivot column, and then select Remove Pivot. ## Troubleshooting pivots - **Red fields in the view and fields with exclamation points in the Data pane**: Because the original fields are replaced with new pivot fields, any references to the original fields in the view will no longer work. They cause fields to turn red in the view or show a red exclamation point next to the field in the Data pane. - **Null values in the grid:** If all of the original fields used in the pivot are removed, for example in an extract refresh, null values display in the pivot fields. - **No pivot option**: Pivot appears when you select two or more columns in a single Microsoft Excel, text file, Google Sheets, and .pdf data source. If using a different data source in Tableau Desktop, you can use custom SQL to pivot. ## Pivot using custom SQL (Tableau Desktop) You can also use custom SQL to pivot your data, even if you aren't working Excel, text file, Google Sheets, and .pdf data sources. 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 | 3/9/2016 15:04 | 3/9/2016 15:25 | | Oscar | 3/9/2016 15:04 | 3/9/2016 15:21 | | William | 3/9/2016 15:04 | 3/9/2016 15:16 | To optimise 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: - Pivots the **Start Time** column header into a string value called **Start** and adds that value to a new column called **Action**. - Pivots the **End Time** column header into a string value called **End** and adds that value to a new column called **Action**. - Pivots the **Start Time** and **End Time** columns so that their values are in a new column called **Time**. The following table shows the results of this custom SQL query.
RunnerActionTime
Amanda

Start

3/9/2016 15:04
OscarStart3/9/2016 15:04
WilliamStart3/9/2016 15:04
AmandaEnd3/9/2016 15:25
OscarEnd3/9/2016 15:21
WilliamEnd3/9/2016 15:16
### 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](https://help.tableau.com/current/public/desktop/en-gb/customsql.htm). 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: - _Static Column_: A comma-delimited list of the columns from _Table_, both dimensions and measures, which should not be included in the pivot. - _New Value (from Column Header 1-3)_: New names that you give to the original column headers, which are used as row values in the pivot. - _Pivot Column Values 1-3_: The columns whose values need to be pivoted into a single column. - _New Column Header_: The name you give the new column that contains the new row values from _New Value (from Column Header 1-3)_. - _New Values_: The name give the new column that contains the original values from _Pivot Column Values 1-3_. - _Table_: The table that you connected to. 4. Click OK.