Dynamically Uncollapse Columns for Tabular Output

jlazerus
jlazerus 🟡
in Dataflows

Hi,

I'm trying to create a stored procedure like in the link below and while its not erroring out, its also not uncollapsing columns like it says it would. I'm not great at SQL and I've tried this in ETL but I can't get it to work there either.

 

https://knowledge.domo.com/@api/deki/files/5209/Dynamic_Pivot.sql?revision=1

 

What I'm trying to do is to take a dataset that looks like this and have 2 different types of cards (a multi-line chart with the EBProjectHealth as the series and a table that looks like what's below)

 

Input:

Screen Shot 2018-10-30 at 7.58.59 PM.png

 

Output 1:

Screen Shot 2018-10-30 at 8.01.51 PM.pngOne row for each ProjectName

 

Output 2: 

Screen Shot 2018-10-30 at 8.02.58 PM.pngCount of Distinct ProjectName for each month, by EBProjectHealth (as scope)

 

When I run the stored procedure, it gives me an output that looks like the original input above with back ticks surrounding the date field. I also tried it on the ProjectName field with the same result.

 

Thoughts?

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ 🟤
    Answer ✓

    I see why.

     

    Your Date field is Text format, not a date.  Can you convert it to a Date earlier in the data flow?  then this should work.


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________

Answers

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    Try creating these two transorms in your data flow:

    number 1:

     

    CREATE PROCEDURE pivot()
    BEGIN
    set @sql_dynamic = (
    SELECT
    group_concat( DISTINCT
    concat(
    'max(case when month(Date)='
    ,month(`Date`)
    ,' and year(Date)='
    ,year(`Date`)
    ,' then EBProjectHealth end) as `'
    ,monthname(`Date`)
    ,' '
    ,year(`Date`)
    ,'`'
    )
    ) as `test`
    FROM `dojo_help_jlazerus`);

    SET @sql = concat('create table pivot as ','Select ProjectName, ',
    @sql_dynamic, '
    FROM dojo_help_jlazerus
    Group by ProjectName WITH ROLLUP'
    );

    PREPARE stmt from @sql;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt;

    END

     

     

     

    number 2:

    CALL pivot()

     

     

    this will create a table named pivot that will pivot all of your months.


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • jlazerus
    jlazerus 🟡

    Thanks for the reply. When I run this, I get this error on running the Call pivot()

     

    The database reported a syntax 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 'NULL' at line 1

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    Did I get the field names correct?

     

    I used "ProjectName", "Date", and "EBProjectHealth"


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • jlazerus
    jlazerus 🟡

    Yes. Those look correct.

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    That's odd.  It works for me.  Does it error out when you run the Create procedure transform too?


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • jlazerus
    jlazerus 🟡

    It does not error when I create the procedure.

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    Maybe I posted an old version of the code.  Try copying this in again?

     

    CREATE PROCEDURE pivot()
    BEGIN
    set @sql_dynamic = (
    SELECT
    group_concat( DISTINCT
    concat(
    'max(case when month(Date)='
    ,month(`Date`)
    ,' and year(Date)='
    ,year(`Date`)
    ,' then EBProjectHealth end) as `'
    ,monthname(`Date`)
    ,' '
    ,year(`Date`)
    ,'`'
    )
    ) as `test`
    FROM `dojo_help_jlazerus`);

    SET @sql = concat('create table pivot as ','Select ProjectName, ',
    @sql_dynamic, '
    FROM dojo_help_jlazerus
    Group by ProjectName WITH ROLLUP'
    );

    PREPARE stmt from @sql;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt;

    END


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • jlazerus
    jlazerus 🟡

    Same error. And just to validate, the only thing I'm changing on your script is the FROM table in the SELECT and SET sections, right?

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    1.png


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • ST_-Superman-_
    ST_-Superman-_ 🟤

    Actually, you can probably drop the "WITH ROLLUP" text as well


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • jlazerus
    jlazerus 🟡

    Those are the fields i'm changing and I also tried without the WITH ROLLUP text and am getting the same error.

  • jlazerus
    jlazerus 🟡

    Good catch. That fixed that error. This is amazing! Thank you so much.

Categories