Tableau Unpivot SQL Table Columns

This Tableau article will show you how to unpivot the SQL table columns with an example. There is no direct option to Unpivot on the Tableau desktop for SQL Database tables however will try to make it simple with a basic example.

The below screenshot will show you the data inside the table.

You can use the below query to get the same data from the adventure works database.

SELECT Name,[2011], [2012], [2013], [2014]
FROM 
(
	SELECT PROD.Name,YEAR(OrdHead.OrderDate) as OrderYear,
           SUM(Details.OrderQty) AS OrderQuantity
    FROM Sales.SalesOrderDetail AS Details
    INNER JOIN Production.Product AS PROD 
       ON Details.ProductID = PROD.ProductID
    INNER JOIN Sales.SalesOrderHeader AS OrdHead 
       ON Details.SalesOrderID = OrdHead.SalesOrderID
     GROUP BY PROD.Name,YEAR(OrdHead.OrderDate)
    ) AS SOURCE
PIVOT( SUM(OrderQuantity) FOR OrderYear 
        IN ([2011], [2012], [2013], [2014])
       ) AS Result
ORDER BY Name

First, connect Tableau to Microsoft SQL Server. Next, choose the required database from the existing list. Finally, drag and drop the Pivot table to see the data inside it.

Tableau Unpivot SQL Table Columns

And if you select all the columns, click on the down arrow to see the options in the context menu. If you see there is a Merge mismatched fields option.

The Merge mismatched fields option selects the first non-Null value instead of unpivot data.

The Output of the Merge mismatched fields

Go to the Data Menu and select convert to custom SQL option or click the down arrow beside the table name and select convert to custom SQL option.

Custom SQL Query in Tableau to Unpivot Table Columns 5

We must write an SQL query using the UNPIVOT function to transform the Tableau Pivot table columns data. Next, click the preview results option to view the output.

SELECT [Product Name]
      ,[Order Year]
      ,[Order Quantity]
FROM [dbo].[PIVOT Transformation] [PIVOT Transformation]
UNPIVOT 
(
    [Order Quantity] FOR [Order Year] 
    IN (
        [PIVOT Transformation].[2011],
        [PIVOT Transformation].[2012],
        [PIVOT Transformation].[2013],
        [PIVOT Transformation].[2014]) 
       ) AS [UNPIVOT TABLE]

Close all the windows to view the output.

Tableau Unpivot SQL Table Columns Result

Let me design a simple stacked horizontal bar chart to display the unpivot data.

Tableau Unpivot using Pivot Option

In Tableau, there is a pivot option to convert the table columns to unpivot data. However, this option works only with text, CSV, or Excel files. You have to write your own custom sql query for the database. In this Tableau section, we use the pivot option to perform unpivot table columns.

The below screenshot shows you the data inside a text file.

Drag and drop the CSV file to view data inside it. Please refer to connect to text file article to view the steps involved in the Tableau connection information.

Click the down arrow and choose field names are in the first row option.

Unpivot using Pivot Option

Select all the columns and click on the down arrow to see the options in the context menu. Please select the Pivot option. For database. input, please refer to Using Custom Query article.

Use Tableau Pivot Option to Unpivot the Columns

We got our data; however, we must rename the columns to meaningful ones. So, right-click on the column and select rename to write the custom name.

Do the same for the remaining columns.

Let me design a simple Tableau chart to show you the data we got from Unpivot table columns.