Pivoting multiple columns to one, while keeping correlating data

Swoopy
Swoopy Member
in Magic ETL

Ok pretty sure I need to creat a pivot table of some kind, to move data that extends in rows across an unknown number of columns, and bring it all into one uniform column. The problem I have is keeping the associated row data when you move the data into one column. See below --->

 

What I have:

 

Screen Shot 2017-08-29 at 11.59.52 AM.png

 

What I need:

 

Screen Shot 2017-08-29 at 11.59.57 AM.png

 

How do I do this?

Tagged:

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    There's a couple of different ways to handle something like this. If all of your data looks like your example, you could import the original dataset and then do a data transform like the one below:

     

    SELECT COL1, COL2, COL3 FROM Dataset
    UNION ALL
    SELECT COL1, COL2, COL4 FROM Dataset
    UNION ALL
    SELECT COL1, COL2, COL5 FROM Dataset

    ORDER BY COL1

     

    (My input dataset)
    image.png

     

     

     

     

     

     

     

    (My results)

    image.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     Just change the column and table names to whatever you end up naming your own table and that should do the trick. 

     

    If you have any questions or problems implementing, just let me know.

     

    **Please mark "Accept as Solution" if this post solves your problem

    **Say "Thanks" by clicking the thumbs up if this post helped you.

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    There's a couple of different ways to handle something like this. If all of your data looks like your example, you could import the original dataset and then do a data transform like the one below:

     

    SELECT COL1, COL2, COL3 FROM Dataset
    UNION ALL
    SELECT COL1, COL2, COL4 FROM Dataset
    UNION ALL
    SELECT COL1, COL2, COL5 FROM Dataset

    ORDER BY COL1

     

    (My input dataset)
    image.png

     

     

     

     

     

     

     

    (My results)

    image.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     Just change the column and table names to whatever you end up naming your own table and that should do the trick. 

     

    If you have any questions or problems implementing, just let me know.

     

    **Please mark "Accept as Solution" if this post solves your problem

    **Say "Thanks" by clicking the thumbs up if this post helped you.

  • Swoopy
    Swoopy Member

    Awesome, thanks for the response. So can I do this using ETL or would I need to do a tradtional SQL transformation?

  • Valiant
    Valiant Coach

    Unfornately the ETL option limits you to only using Joins. So this would need to be a MySQL transformation in order to do it this way.

     

    If you have any issues creating the SQL Data flow, don't hesitate to ask.

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the thumbs up if this post helped you.

  • Swoopy
    Swoopy Member

    So it doesn't look like I have access to the MySQL transform tool in Domo. Reaching out to support to get this activated. Thanks for the help. 

  • Sambo_Servicer
    Sambo_Servicer Member

    Thanks, Valiant Spur. This helped me out in a major way.

Categories