Pivoting multiple columns to one, while keeping correlating data

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?

Find more posts tagged with

Magic ETL

Table

Accepted answers

BlueRooster

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)

(My results)

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.

All comments

BlueRooster

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)

(My results)

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

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

BlueRooster

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

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

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