So I've searched several times for a solution to be able to pivot data in Retool. It's fairly complicated to do it in SQL (Snowflake) and have it be dynamic and the Retool Table component doesn't seem to have that feature available (and no plans on the road map according to other posts) and doing it in JavaScript seems even more challenging. So it occurred to me that the only issue in Snowflake is having the PIVOT column be dynamic. But with Retool being able to dynamically write SQL queries that can create views in Snowflake, that problem can be solved using a transformer that creates the header row of the pivot with that column's values all separated by commas.
My initial issue was wanting to display annual data with years going across the top from left to right and the other descriptor columns going down the left side from top to bottom and then the values filling the remaining cells in the appropriate columns and rows (according to description and year). But my years change often based on length of my forecast or simply as time goes on, the years that we forecast change.
So first step was to create a view in Snowflake that gives me a list of all the months between a start date and an end date. This was done using the following SQL:
create or replace view FORECAST_MONTHS(
DATE_MONTH
) as
WITH CTE AS
(
SELECT
TO_DATE(CONCAT(START_YEAR,'-01-01')) AS VALUE
FROM FORECAST_DATES
UNION ALL
SELECT
DATEADD(MONTH, 1, VALUE)
FROM CTE
LEFT JOIN FORECAST_DATES
WHERE DATEADD(MONTH, 1, VALUE) <= TO_DATE(CONCAT(END_YEAR,'-12-31'))
)
SELECT
*
FROM CTE;
My FORECAST_DATES table has the following structure:
create or replace TABLE FORECAST_DATES (
START_YEAR NUMBER(38,0),
END_YEAR NUMBER(38,0)
);
Then from these results I can use the following SQL to get a distinct list of years:
create or replace view FORECAST_YEARS(
DATE_YEAR
) as
SELECT DISTINCT
YEAR(FORECAST_MONTHS.DATE_MONTH) AS DATE_YEAR
FROM FORECAST_MONTHS
ORDER BY DATE_YEAR;
Now I have results from this view that give me a distinct list of years from 2022 through 2050. This what I need to select from in Retool to get my years for my transformer. In Retool my query looks like the following and is named 'selectForecastYears':
SELECT
*
FROM FORECAST_YEARS
ORDER BY
DATE_YEAR
;
This was placed in a Retool Resource that has Dynamic SQL Queries Enabled (basically meaning you can shift control of what years are used from being in Snowflake SQL code to be an input from a Retool component).
Then I created a transformer that takes those results and separates each year by a comma and creates one big string out of it and that transformer is called 'transformerForecastYears'.
var yearsArr = {{ formatDataAsArray(selectForecastYears.data) }}
const years = yearsArr.flatMap(({ DATE_YEAR }) => DATE_YEAR)
let yearsFormatted = years.join(", ")
return yearsFormatted
Now I am ready to write my create query that will update the view in Snowflake with the years from forecast. I basically have a table called RESOURCES that contains every power plant that I am modeling separately outside of Snowflake and Retool. I also have a RESOURCE_UNIT_DATES table that contains each power plants commission date and retire date, Basically, when it starts operating through when it is expected to stop operating. And I have some descriptors in a a few other tables that contain attributes about those plants, like Balancing Authority, State, Area, etc.
CREATE OR REPLACE VIEW ANNUAL_DATA AS
SELECT
*
FROM (
SELECT
R."BalancingAuthority" AS BALANCING_AUTHORITY,
R."State" AS STATE,
R."TechType" AS TECHTYPE,
SUM(R."MaxCapValue") AS CAPACITY,
FY.DATE_YEAR
FROM RESOURCE AS R
LEFT JOIN RESOURCE_UNIT_DATES AS RUD
ON R."Name" = RUD."Resource"
LEFT JOIN AREAS AS A
ON R."Area" = A.MODEL_AREA
LEFT JOIN FORECAST_YEARS AS FY
ON TO_DATE(CONCAT(FY.DATE_YEAR, '-', A.PEAK_MONTH,'-01')) BETWEEN TO_DATE(RUD."CommissionDate") AND TO_DATE(RUD."RetirementDate")
GROUP BY
R."BalancingAuthority",
R."State",
R."TechType",
FY.DATE_YEAR
ORDER BY
FY.DATE_YEAR,
R."BalancingAuthority",
R."State",
R."TechType"
) PIVOT(SUM(CAPACITY) FOR DATE_YEAR IN ({{ transformerForecastYears.value }}))
ORDER BY
BALANCING_AUTHORITY,
STATE,
TECHTYPE
;
Now that I have updated this pivot view with the right years, I simply need to execute it and then use it's results as the data source of my table.
SELECT DISTINCT
*
ANNUAL_DATA
ORDER BY
BALANCING_AUTHORITY,
STATE,
TECHTYPE
;
You will need to make sure you line all these up in a JavaScript query in Retool and use the await command on each so that they run in sequence.
- Run the view that updates your start date and end date (not included here).
- Run the view that selects those years into Retool
- Run the create pivot table view that uses the transform from the results of 2 above.
- Select the pivoted results into Retool.
- Refresh the table.
Now there are some more 'smart adds' I could do in JavaScript to further manipulate my table to hide years that don't have data or that were used in the last time the create pivot view was run but aren't there this time. Hope this helps!