Single column dynamic pivot - syntax error

har_d_har1

Grateful for any guidance. I'm seeing the syntax error below and I can't figure out why. New to MySQL Transforms as my previously relied upon python scripts are to be deprecated.

Objective

To take a single column of dates and turn them into column headers. Once pivoted, row value(s) are irrelevant (they'll be overwritten later).

Desired Result

Input Dataset

The MySQL Dataflow

/Dynamic Reverse Pivot/


CREATE PROCEDURE Pivot()


BEGIN
SET @sql = NULL;


SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN dt = ''',dt,''' THEN ''',y,''' ELSE 0) AS ''', dt,''' ')) INTO @sql FROM preformat;


SET @sql = CONCAT('SELECT p.y,', @sql, 'FROM preformat p GROUP BY p.y');


PREPARE stmt FROM @sql;


EXECUTE stmt;


DEALLOCATE PREPARE stmt;


END

The Error

MichelleH

@har_d_har1 Before you do that, I recommend reading this Knowledge Base article on Gantt chart cards, which provides guidance on how to structure data for these types of visualizations: https://domo-support.domo.com/s/article/360043429133?language=en_US

MichelleH

@har_d_har1 I generally advise against pivoting by date in a dataflow because it is very cumbersome to manipulate a constantly growing number of columns. May I ask how you intend to use this data so we can suggest another transformation method?

har_d_har1

@MichelleH The data will be used to generate a card like this. What I have going in are start and end dates of various activities, and a total amount of effort to complete the activity. I'll want to display each activity in its own row in the chart.

I'm aware of the issues with removing/adding table columns via ETL, so I create the dataset with two years of columns, and change the date columns once a year

MichelleH

@har_d_har1 Before you do that, I recommend reading this Knowledge Base article on Gantt chart cards, which provides guidance on how to structure data for these types of visualizations: https://domo-support.domo.com/s/article/360043429133?language=en_US