Dynamic Pivot

@NathanDorsch Can you share a little bit more about what you want to do with the data once it's pivoted? In cases like this, I generally find the most success in leaving the dataset as-is and doing the actual pivoting in the cards.

https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns

The only way to dynamically pivot a table would be to utilize a MySQL dataflow and do something like this:

I found this article which provides essentially the same solution and walks me how to do this via mysql. I've never used mysql in here before though… and the directions are a bit confusing to me. Can you advise what I'm supposed to do? I first created an output dataset which reduces the columns down to just two columns: Retailer-Field-Target-Priority and Target Value

Now I've opened up mysql with the dataset…

When I click on Add Transform, I get two options: Table and SQL. I went with SQL and created the first transformation with SELECT concat('',Retailer-Field-Taget-Priority,'') as RFTP,
Target Value
from salsify_product_requirements_2_columns

Now… I suppose I create another transformation and the example code they provided is

/Dynamic Reverse Pivot/
CREATE PROCEDURE Pivot()
BEGIN

SET @cols =
(SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(p.Account = ''', Account, ''', p.Amount, NULL)) AS ', Account)
) AS Base
FROM 1st_transform);

SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT p.Region , p.Month, p.Year, p.Vertical, ', @cols, '
FROM 1st_transform p
GROUP BY p.Region, p. Month, p.Year, p.Vertical'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

/* CAll Store Procedure */

CALL Pivot()

Can you advise on how to adjust this code to fit my data? I'm a little lost here…

And then what is the next step?

Could somone possibly jump on a call to walk me through this? I feel like this is something that should probably only take a few mins for someone who knows what they're doing. But it take me hours of headaches until I give up…

Just bumping this…. can anyone help me? maybe @GrantSmith

Thanks.

@NathanDorsch Your first transform with the SELECT statement should be in a Table transform, not SQL. Whenever you need to create a subquery, you need to use a Table transform in order to name the table and reference it in subsequent transforms. SQL transforms are for actions that don't require an output table.

Thanks. I did end up switching over to using the Table transform. Still having issues though…

@NathanDorsch What issues are you seeing?

Would you possibly be willing to jump on a call with me?

@MichelleH I ended up taking it in many different directions and honestly can't tell you what is happening, I just keep getting errors saying "whoops, something went wrong" or other things… It'd probably be a lot easier if I can just walk you through it and hoepfully you'll discover what I'm doing wrong pretty quickly.

Well you can check my availability here. If you or anyone can schedule a quick call with me to walk me through this, I'd be very grateful. Or if someone knows the person who wrote this article on how do this and can point me to him/her: https://domo-support.domo.com/s/article/360042923454?language=en_US

Thank you.

@NathanDorsch Unfortunately I'm not able to get on a call. If you post some screenshots of where the errors are occurring we may be able to help identify the issue

Hi @NathanDorsch, I was able to get some time this weekend to dig in a bit deeper to your issue.

You'll want to use the following code to do a dynamic pivot using a MySQL dataflow. Replace the nathandortch_pivot_table_example in your code to the name of your input dataset name.

The first transform you'll have is a table transform to format your values if necessary. Make sure it's called preformat

select Retailer, Field, Target, Strategic Priority, Target Value

from nathandortch_pivot_table_example

The second transform is a SQL transform to clear out the procedure if it already exists:

DROP PROCEDURE IF EXISTS Pivot;

The third transform is another SQL transform to create you dynamic pivot table. This was built off your example dataset.

/Dynamic Reverse Pivot/

CREATE PROCEDURE Pivot()

BEGIN
SET @cols =

(SELECT

GROUP_CONCAT(DISTINCT

CONCAT('MAX(IF(p.Target = ''', Target, ''', p.Target Value, NULL)) AS ', Target)

) AS Base

FROM preformat);
SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT p.Retailer , p.Field, p.Strategic Priority,', @cols, '

FROM preformat p

GROUP BY p.Retailer , p.Field, p.Strategic Priority'));

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;
END

The next transform is another SQL transform to call the procedure and create the table with the data

CALL Pivot();

The next transform is a Table transform to pull the data into a domo data table. This should be called transform_data_1

select *

from Pivot

Finally under output dataset pull the data from the transform_data_1 table.

select * from transform_data_1

You should then have something that ends up looking like this:

Screenshot 2023-05-06 at 7.44.20 PM.png

Hope this help clear things up for you.

Thanks @GrantSmith I'm having issues with the 4th step: Call Pivot(); I get an invalid error message: Procedure dataflow_schema.Pivot does not exist

Actually, it looks like I'm having issues with the third step:

Looks like my copy and paste was bitten by the forum formatting.

Remove the first line and it should work. /Dynamic Reverse Pivot/

Alternatively you can change it to what it should be /*Dynamic Reverse Pivot*/

the /*…*/ just represents a commend to add some contextual information but doesn't do anything to the actual code.

that worked! But now the 4th one is still failing:

More auto-formatting issues. Try this version for your procedure definition.

/*Dynamic Reverse Pivot*/

CREATE PROCEDURE Pivot()

BEGIN
SET @cols =

(SELECT

GROUP_CONCAT(DISTINCT

CONCAT('MAX(IF(p.`Target` = ''', `Target`, ''', p.`Target Value`, NULL)) AS ', `Target`)

) AS Base

FROM `preformat`);
SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT p.`Retailer`, p.`Field`, p.`Strategic Priority`,', @cols, '

FROM preformat p

GROUP BY p.`Retailer`, p.`Field`, p.`Strategic Priority`'));

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;
END

Alternatively, I've attached a txt file with the procedure code.

proc.txt