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:
What I need:
How do I do this?
Best 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 DatasetORDER 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.
2
Answers
-
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 DatasetORDER 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.
2 -
Awesome, thanks for the response. So can I do this using ETL or would I need to do a tradtional SQL transformation?
0 -
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.1 -
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.
0 -
Thanks, Valiant Spur. This helped me out in a major way.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive