Pivot SQL Server data using Temporary Tables on Tableau

Deal with read-only SQL Server database, Pivot Query, Tableau altogether using Temporary (#temp) tables

I recently happened to build a Tableau dashboard based on the data coming out of a read-only SQL Server database, i.e. I can not create tables on the database. How did I handle that?

Problem:

Let’s assume my Executive Management would like to see a visual representation of all the projects an individual is assigned to along with weekly hours the resource is going to spend on the project. Now, let’s see the data availability and the expected output below:

Input Data

Expected Output Data

Solution:

Step1: Create the Project Details table (Note that this is not needed in real world as production data). As it is read-only database, I create temporary table as #TEMP1.

/* Step1: Create a dummy data table (This data would be from your production tables in real world) */
DROP TABLE IF EXISTS #TEMP1;
CREATE TABLE #TEMP1 (Person VARCHAR(MAX), Project VARCHAR (MAX), Project_Start_Date DATE, Project_End_Date DATE, Estimated_Time_Spent INT);
INSERT INTO #TEMP1
SELECT 'Cruise', 'Knight and Day', '2022-01-02', '2023-01-02', 3000 UNION ALL
SELECT 'Cruise', 'Edge of Tomorrow', '2022-01-09', '2022-09-16', 2500 
UNION ALL
SELECT 'Cruise', 'Firm', '2021-06-15','2022-01-01', 2000 
UNION ALL
SELECT 'Smith', 'Legend', '2022-09-20', '2023-04-18', 1000 
UNION ALL
SELECT 'Smith', 'MIB', '2022-08-01', '2023-03-29', 2000
;

Step2: Create Weekly Dates table (Note that there are tons of ways to create this For example, using While loop etc). However, I chose to hard code them for simplicity into another temporary table as #TEMP2.

/* Step2: Create a Temporary Table with all the week start dates populated */
DROP TABLE IF EXISTS #TEMP2;
CREATE TABLE #TEMP2 (WeekOf DATE, WeekofMod VARCHAR(MAX));
INSERT INTO #TEMP2
SELECT '2022-08-15','W20220815' UNION ALL
SELECT '2022-08-22','W20220822' UNION ALL
SELECT '2022-08-29','W20220829' UNION ALL
SELECT '2022-09-05','W20220905' UNION ALL
SELECT '2022-09-12','W20220912' UNION ALL
SELECT '2022-09-19','W20220919' UNION ALL
SELECT '2022-09-26','W20220926' UNION ALL
SELECT '2022-10-03','W20221003' UNION ALL
SELECT '2022-10-10','W20221010' UNION ALL
SELECT '2022-10-17','W20221017' UNION ALL
SELECT '2022-10-24','W20221024' UNION ALL
SELECT '2022-10-31','W20221031' UNION ALL
SELECT '2022-11-07','W20221107' UNION ALL
SELECT '2022-11-14','W20221114' UNION ALL
SELECT '2022-11-21','W20221121' UNION ALL
SELECT '2022-11-28','W20221128' UNION ALL
SELECT '2022-12-05','W20221205' UNION ALL
SELECT '2022-12-12','W20221212' UNION ALL
SELECT '2022-12-19','W20221219' UNION ALL
SELECT '2022-12-26','W20221226';

Step3: A)Join #TEMP1 and #TEMP with the condition weekly dates between Project Start and End date. B)Pivot the joined data to get required output (i.e. one row per project with weekly budget). C) Output to a new temporary table as #TEMP3.

/* Step3: Join Project Data table with Weekly dates table and PIVOT the data and write to a temporary table to be used in Tableau */
DROP TABLE IF EXISTS #TEMP3;
SELECT * INTO #TEMP3 FROM
(SELECT 
 Person
 ,Project
 ,Project_Start_Date
 ,Project_End_Date
 ,Estimated_Time_Spent
 ,DATEDIFF(DAY, Project_Start_Date, Project_End_Date) AS Num_Of_Days
 ,FLOOR(DATEDIFF(DAY, Project_Start_Date, Project_End_Date)/7) AS     
  Num_Of_Weeks
 ,ROUND(Estimated_Time_Spent/(FLOOR(DATEDIFF(DAY,  
  Project_Start_Date, Project_End_Date)/7)),2) 
  AS Estimated_Weekly_Hours
 ,T2.WeekofMod
 FROM  #TEMP1 T1
 LEFT JOIN #TEMP2 T2
 ON  T2.WeekOf BETWEEN T1.Project_Start_Date AND T1.Project_End_Date
) ONE
PIVOT
/* As SQL doesn't accept the column names starting with numbers ( For example, 2022-08-15), modified date column WeekOfMod is used below */
(MAX(Estimated_Weekly_Hours) FOR WeekofMod IN
(W20220815, W20220822, W20220829, W20220905, W20220912, W20220919,
 W20220926, W20221003, W20221010, W20221017, W20221024, W20221031,
 W20221107, W20221114, W20221121, W20221128, W20221205, W20221212,
 W20221219, W20221226)
) PIVOT_TABLE
ORDER BY Person, Project_Start_Date;

Step4: Copy all the code (until creation of #TEMP3) into “Initial SQL” window of Tableau.

Step5: Now the final select statement goes into “Edit Custom SQL” window of Tableau.

Result:

Output on Tableau

Conclusion:

1. No matter how complex the logic is, the output can be written to Temporary tables. This is your best friend in case of read-only databases (where create statements are not allowed).
2. Use “Initial SQL” window of Tableau in case of PIVOT queries and final SELECT statement in “Edit Custom SQL” window of Tableau.