Using a Postgres dataflow to split a comma-separated string into separate rows

user07641 · December 2017

Trying to split a comma-separated field into rows. Input looks like this:

JiraID | Zendesk IDs

1 | 5,6,7

Desired Output:

Jira ID | Zendesk ID

1 5

1 6

1 7

tried using unnest(string_to_array("Zendesk IDs", ',')::integer[])

but am getting errors that unnest(integer[]) not found.

Any other way?

Thanks!

Chronos · December 2017

Is anyone able to help out with this request?

Quirolla · June 2018

I am trying to acomplish the same thing and am using https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Splitting_a_String_into_Multiple_Rows

Issue is in the instructions they do not result in a final table as indicated after the last step and it does not seperate by comma.

DVLuna · August 2018

I need this solution as well.

I also concur that the linked suggestion does not result in any "final" table that I can locate.

Can anyone support?

AjaiRup · November 2018

As Quirolla mentioned, this article should help: https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Splitting_a_String_into_Multiple_Rows

If you'd like to create new columns instead of rows you could use the SUBSTRING_INDEX() function, here is a good article on how to use it: https://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php

Quirolla & DVLuna,

For some reason you cannot see the table 'final' as an option on the left pane, however, if you use the following query (as a transform or an output) you'll be able to see the results:

SELECT *

FROM final

I assume it's because the table 'final' is generated within the Stored Procedure, but I could be wrong there.

Hope that helps!

Using a Postgres dataflow to split a comma-separated string into separate rows

Comments

Categories