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

user07641

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

Is anyone able to help out with this request?

Quirolla

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

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

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!