I am trying to prep some data before using it in Tableau. There's a field that I need to clean-up and split out into unique line items. Using the Custom SQL options I'd like to split the string up with each substring duplicating all other values into a new row.
Here's what the data looks like (the delimiter is actually | but due to how tables are made here I used a - instead):
| ID | Goal | Other fields etc. |
|---|---|---|
| 123456 | Goal A - Goal B | etc. |
| 234567 | Goal A | etc. |
| 345678 | Goal B - Goal C - Goal D | etc. |
Output I want:
| ID | Goal | Other fields etc. |
|---|---|---|
| 123456 | Goal A | etc. |
| 123456 | Goal B | etc. |
| 234567 | Goal A | etc. |
| 345678 | Goal B | etc. |
| 345678 | Goal C | etc. |
| 345678 | Goal D | etc. |
My current SQL statement is simple and just looks like:
SELECT
database."Id" As "ID",
database."Goals" As "Goals",
etc.
FROM database
I've seen some posts that this could be accomplished by Cross Apply Split String, but have also read that this isn't usable with MySQL. And when I plugged in the below I got a syntax error.
SELECT
database."Id" As "ID",
database."Goals" As "Goals",
etc.
FROM database
CROSS APPLY SPLIT_STRING(database."Goals", "|")
EDIT: I've viewed SQL split values to multiple rows and this solution specifies that there is a maximum allowance. I'd like to see if there's a solution where there isn't a maximum limitation.
