regexp_split_to_table and row_number

Viewed 9848

I have table with string data like this:

id | string_data
1  | red;green;blue
2  | orange
3  | purple;cyan

And I need split string data to items with row numbers:

id | num | item
1  | 1   | red
1  | 2   | green
1  | 3   | blue
2  | 1   | orange
3  | 1   | purple
3  | 2   | cyan

I know regexp_split_to_table() but I have problem to combine it with row_number(). Does anybody know how to solve this? Thanks!

2 Answers
Related