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, I will try to make it simple with a basic example.
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
The below screenshot will show you the data inside the table, and it has 266 records. 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
If you select all the columns, click 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.
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.
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.
Let me design a simple stacked horizontal bar chart to display the unpivot data.
Tableau Unpivot using the 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 the connect to text file article to view the steps involved in the Tableau connection information. Next, click the down arrow beside the Unpivot Source. csv and choose field names in the first row option.
Unpivot using the 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 the Using Custom Query article.
We got our data; however, we must rename the columns to meaningful ones. So, right-click on the column will open the context menu shown in the above image. Here, we must select the Rename option 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 the Unpivot table columns.
