Dynamic Pivot

NathanDorsch

I have a table with a combination of fields and a target value. I want to concat all the fields (Retailer, Field, Target, Strategic Priority) and pivot the table so they are all columns with their respective Target Value underneath.

I know how to do this with the Pivot option in which I manually create a new column for each combination. However, is there a way to have this automatically create new columns? My concern (beyond a whole bunch of manual input - I currently have 67 rows) is there will be rows (with new field combinations) added to this dataset and they'll be excluded unless I recognize and manually add them in.

Below is a screenshot of the data I'm working with for context.

MichelleH

@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.

GrantSmith

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

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

NathanDorsch

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?

NathanDorsch

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…

NathanDorsch

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

Thanks.

MichelleH

@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.

NathanDorsch

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

MichelleH

@NathanDorsch What issues are you seeing?

NathanDorsch

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

NathanDorsch

@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.

NathanDorsch

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.

MichelleH

@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

GrantSmith

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.

NathanDorsch

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

NathanDorsch

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

GrantSmith

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.

NathanDorsch

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

GrantSmith

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

NathanDorsch

@GrantSmith I got all the transformations to run. However, when I look at the resulting dataset, it doesn't have the data pivoted like I wanted… it looks the same as the input dataset.

My whole objective is to convert a concatenated value of Retailer-Field-Strategic Priority-Target set up as columns - and just one row of the Target Value values.

Laurenamcc

https://community-forums.domo.com/main/discussion/comment/65283#Comment_65283

I am new to using MySQL. Trying to follow along but am running into similar errors as seen in this comment. I have a similar situation but do not need to combine any values like OP does. @NathanDorsch did you get this figured out or @GrantSmith would you possibly be able to help?

I have a dataset that looks like this:

Id	FieldName
1	Name1
2	Name2
3	Name3
4	Name4
5	Name5
6	Name6
7	Name7
8	Name8

That I would like to look like this, the FieldNames will change over time so the pivot needs to be dynamic:

Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8
1	2	3	4	5	6	7	8

My third SQL Transform is running successfully:

CREATE PROCEDURE Pivot()

BEGIN

SET @cols =

(SELECT

GROUP_CONCAT(DISTINCT

CONCAT('MAX(IF(p.`FieldName` = ''', `FieldName`, ''', p.`Id`, NULL)) AS ', `FieldName`)

) AS Base

FROM preformat);

SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT *,', @cols, '

FROM preformat p'));

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END

When I run the Transform

CALL Pivot();

I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Type,MAX(IF(p.`FieldName` = 'Description of Accident', p.`Id`, NULL)) AS Descrip' at line 1

Any thoughts on what I am doing wrong? Thanks in advance for looking at this.

art_in_sky

Can someone confirm if the above code worked for the dynamic pivot?

@MichelleH @GrantSmith @NathanDorsch