1

I'm using sql query on redshift I have a table(@tA) having more than one columns with comma separated values - 

create table* @tA 
(
    col1 varchar(100),
    col2 varchar(100),
    col3 varchar(100)
)

@tA -

col1 |  col2  |   col3
a1      b1        c1
a2      b2,b3     c2,c3,c4
a3      b4        c5

I want the final result like this -

col1  col2  col3
a1     b1   c1
a2     b2   c2
a2     b2   c3
a2     b2   c4
a2     b3   c2
a2     b3   c3
a2     b3   c4
a3     b4   c5

can anyone help me

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
Durgesh panwar
  • 19
  • 1
  • Are you looking for a one-time conversion to create additional records? Or a query that will break down the comma-separated values on the fly? – Alex Howansky Jan 04 '18 at 14:13
  • Hi, i tried so many things but some of them didn't work on my sql server .i referred this below link also but here they assumed number of rows are greater than maximum number of comma separated values in any column- https://stackoverflow.com/questions/25112389/redshift-convert-comma-delimited-values-into-rows – Durgesh panwar Jan 04 '18 at 14:22
  • Hi Alex Howansky, i'm not looking for one time conversion , it would be better if you can calculate the number of rows and maximum number of comma separated values by using any parameter or variable because the number of comma separated values in any column could be any thing in my table – Durgesh panwar Jan 04 '18 at 14:30
  • cross apply didn't work in my sql server – Durgesh panwar Jan 04 '18 at 14:35
  • 1
    Possible duplicate of [Redshift. Convert comma delimited values into rows](https://stackoverflow.com/questions/25112389/redshift-convert-comma-delimited-values-into-rows) – Yusuf Hassan Jan 04 '18 at 15:25

0 Answers0