1

Right now, I have a bunch of data stored in a field and it's a series of numbers separated by commas:

ID      Values 

235077  8185,8186,8187,8188,8189,8190,8191,9241,9244,9245,1,2,3,4,5,9
246576  1,2,3,4,5,9
114507  1,2,3,4,5,6,9
256493  8166,8167,8168,8169,8170,8171,8172,20198,20201,20202,20204,20205,20207,20209,1,2,3,4,5,9
256518  1,2,3,4,5,6,9
234587  1,2,3,4,5,9
256513  1,2,3,4,5,9
242822  8166,8167,8168,8169,8170,8171,8172,20198,20201,20202,20204,20205,20207,20209,1,2,3,4,5,9
256510  1,2,3,4,5,9
183173  1,2,3,4,5,9
256463  8185,8186,8187,8188,8189,8190,8191,9241,9244,9245,1,2,3,4,5,9
255102  1,2,3,4,5,9
253850  1559,1560,1561,1562,2728,1,2,3,4,5,9
240286  9239,9242,1,2,3,4,5,8
64154   1,2,3,4,5,9
232844  1,2,3,4,5,9
246571  4317,1,2,3,4,5,9

What I would like to do is manipulate the data in such a way where I can not only separate the values out, but pull them all into one column that corresponds with their IDs. So using 64154 as an example:

ID     Values
65414     1
65414     2
65414     3
65414     4
65414     5
65414     9

I'm sure this would involve some sort of Regex function, but in terms of cascading the data in the manner above has me lost.

wizkids121
  • 634
  • 1
  • 9
  • 22
  • Are you using Postgres or Redshift? – Gordon Linoff May 08 '18 at 20:07
  • similar question with a good answer https://stackoverflow.com/questions/49994931/how-to-get-substring-for-filter-and-group-by-clause-in-aws-redshift-database/49999374#49999374 – AlexYes May 08 '18 at 20:16
  • It's Redshift I'm working with – wizkids121 May 08 '18 at 21:08
  • Do you know the maximum number of pieces of data that can appear? You can hard code a bunch of `SPLIT_PART` to extract them, and use a `CROSS JOIN` to turn it into many rows https://docs.aws.amazon.com/redshift/latest/dg/SPLIT_PART.html – Nick.Mc May 08 '18 at 23:15
  • The below post helps with a solutiosn for this problem https://stackoverflow.com/questions/28975066/error-while-using-regexp-split-to-table-amazon-redshift – Mukund May 09 '18 at 12:10
  • Possible duplicate of [Redshift. Convert comma delimited values into rows](https://stackoverflow.com/questions/25112389/redshift-convert-comma-delimited-values-into-rows) – Nathan Griffiths May 12 '18 at 21:55

2 Answers2

0

Note: This question was tagged with Postgres when I answered it.

In Postgres, this is easy. You can use a lateral join:

select t.id, r.val
from t, lateral
     regexp_split_to_table(v.x, ',' ) r(val);

I don't think Redshift has this functionality.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Simply use the function UNNEST(). This is a built in function in PostgreSQL which allows for exploding arrays into rows for each array element, as shown below: 

SELECT
id
, UNNEST(values)
FROM a_table

This is eliminates the need to do a lateral join etc.

Rupert
  • 150
  • 1
  • 6