Data + Science

6/29/2014
SQL Code to Prepare Data for Sankey in Tableau

I continue to get regular emails about building sankey diagrams and sankey-style slopegraphs in Tableau based on the instructions that I posted here. I previously posted an Excel VBA for preparing data for Tableau here. A big thank you to Russell Spangler for creating this SQL code that basically does the same thing.

Sample data:

In this example there are 5 rows of data with 2 rankings, position 1 and position 2 followed by some random fields of junk data. To create the curves in Tableau I use 49 points, which means every row of data needs to be converted into 49 rows of data. In addition, there needs to be an additional column t with values ranging from -6 to 6 in increments of .25 (which is 49 values). For example, the State of America Ranking has 50 curves, one for each state totalling 2,450 rows of data. The Redesign of My Monthly Energy Bill from Duke Energy has 100 curves in the sankey diagram, 1 for each % up to 100%, which totals 4,900 rows.

The SQL code below will do this transformation automatically so that the data can be immediately imported into Tableau.


IF OBJECT_ID('tempdb..#Sample','U') IS NOT NULL DROP TABLE #Sample

CREATE TABLE #Sample (
				Category varchar(25)
				,Position1 int
				,Position2 int
				,RandomField1 varchar(25)
				,RandomField2 varchar(25)
				,RandomField3 varchar(25)
				     ) 
GO 

INSERT INTO #Sample VALUES ('a', 1, 2, 'x', 'sdf', 'jdfg')
INSERT INTO #Sample VALUES ('b', 2, 1, 'x', 'asd', 'ewr')
INSERT INTO #Sample VALUES ('c', 3, 5, 'y', 'aaxfg', 'aeja')
INSERT INTO #Sample VALUES ('e', 4, 4, 'y', 'sgj', 'ftua')
INSERT INTO #Sample VALUES ('f', 5, 3, 'x', 'asuser', 'uadrf')
GO
--SELECT * FROM #Sample
--49

IF OBJECT_ID('tempdb..#Increment','U') IS NOT NULL DROP TABLE #Increment

DECLARE @LowRange DECIMAL(19,2)
DECLARE @HighRange DECIMAL(19,2)
DECLARE @Increment DECIMAL(19,2)

SET @LowRange = -6.00 
SET @HighRange = 6.00
SET @Increment = .25

/*
PRINT @LowRange
PRINT @HighRange
PRINT @Increment
*/

;WITH CTE 
AS
(
  SELECT @LowRange as Number
  UNION ALL 
  SELECT CAST(Number + @Increment as decimal(19,2))
  FROM CTE
  WHERE Number < @HighRange
)      
SELECT * 
INTO #Increment
FROM CTE

SELECT * 
FROM #Sample 
CROSS JOIN #Increment

After running this SQL code there will be 49 rows for each original row of data and a new column for t with the values populated. Download the SQL code here.

I hope you find this helpful. If you have any questions feel free to email me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer

Follow on Twitter @HighVizAbility