Pivot for SQL Data (Using Custom SQL) in Tableau

Suganthy E – Associate Consultant

Are you unable to pivot in Tableau when connected to SQL data? No worries!

It is possible to pivot data using a custom SQL query - by using the "UNION ALL" operator. This must be a very common issue to encounter while working on projects that use SQL data.

Let's use the below table as an example of your data table structure:

Data structure

The objective is to get all the Managers and Reps into one column. This would be a very straightforward process had the data been in Excel. You’d simply select the columns in Tableau and pivot them. This isn't as easy with data connected to SQL.

To perform this on the above data, we used the custom SQL query below:

Custom SQL query:

		  SELECT Date, Segment, Seg_subtype,Metric1,
'Type-1 Manager' AS Managertype,
[Type-1 Manager] AS Manager,
'Rep 1' AS Reptype,
[Rep 1] AS Rep,
FROM table1
UNION ALL
SELECT Date, Segment, Seg_subtype,Metric1,
'Type -2 Manager' AS Managertype,
[Type-2 Manager] AS Manager,
'Rep2' AS Reptype,
[Rep2] AS Rep,
FROM table1
UNION ALL
SELECT Date, Segment, Seg_subtype,Metric1,
Type-3 Manager AS Managertype,
[Type-3 Manager] AS Manager,
'Rep3' AS Reptype,
[Rep3] AS Rep,
FROM table1
UNION ALL
SELECT Date, Segment, Seg_subtype,Metric1,
'Type-3 Manager' AS Managertype,
[Type-3 Manager] AS Manager,
'Rep4' AS Reptype,
[Rep4] AS Rep,
FROM table1

Limitations:

The challenge here is four types of Manager and Reps affiliate to metric 1. After using the above query, the resulting pivot table had generated Metric1 into four rows (for four types of managers and reps):

Resulting Pivot Table

This is a common issue sometimes faced after data transformation on a complex dataset. As long as Managers and Reps are used in the views in Tableau, the Metric1 values are accurate. Removing the dimensions and using Metric1 in calculations will cause duplications and an incorrect value. This can be fixed using Tableau’s Level of Detail calculations. The calculation below will fix Metric1 at a Manager Type, Manager and Rep level to ensure that Metric1 is counted only once.

		   (MAX ({INCLUDE [Manager type],[Manager],[Rep]:sum([Metric 1])})
			

Using this calculation instead of Metric1 solves the issue of duplications caused by Pivoting.

Along with the SQL Query to pivot the data and LOD calculations to fix the duplications – you now have a perfect solution to pivot SQL data for Tableau.

High-Impact dashboard. Fast and affordable.

Starts at $1500