sql server - How to split multiple comma-separated value that are in columns to new columns -

i got same / similar problem in 'how split comma-separated value columns' , solution code needs more flexible.

i got multiple (n) columns comma separated values. e.g.:

column1        column2 ====================== a1, a2, a3     er, asw, a2, a3         qwe, qw  

what need this

column1_1 column1_2 columm1_3   column2_1 column2_2 column2_3 ================================================================== a1        a2        a3          er        asw       a2        a3        null        qwe       qw        null 

so depending on columns "longest" (most comma seperated values) need new columns same name , index (here 1-3). if 1 cell has less maximum values (e.g. in case 2) remaining new columns should not contain values respecectively null.

i hope me this!

thanks lot!

edit:
tried using code. works far, not flexible , taking 1 column instead of "n columns"

declare @xml xml  select @xml = ( select cast( '<i id="' + cast(column1 nvarchar(10)) + '"><w>' + replace(nid,' ','</w>    <w>') + '</w></i>' xml) table4 xml path('') )  select       t.v.value('w[1]','nvarchar(100)') string1,     t.v.value('w[2]','nvarchar(100)') string2,     t.v.value('w[3]','nvarchar(100)') string3,     t.v.value('w[4]','nvarchar(100)') string4,     t.v.value('w[5]','nvarchar(100)') string5,     t.v.value('w[6]','nvarchar(100)') string6,     t.v.value('w[7]','nvarchar(100)') string7 @xml.nodes('/i') t(v) 

try this:

it might necessary wrap output columns ltrim(rtrim(...)) rid of trailing spaces.

and might necessary replace three characters of evil, if expect them within strings (<>; should < > , &)

declare @tbl table(column1 varchar(100),column2 varchar(100)); insert @tbl values  ('a1, a2, a3','er, asw, as') ,('a2, a3','qwe, qw');  splitted (     select cast('<x>' + replace(column1,',','</x><x>') + '</x>' xml) col1xml           ,cast('<x>' + replace(column2,',','</x><x>') + '</x>' xml) col2xml     @tbl ) select col1xml.value('/x[1]','varchar(max)') column1_1       ,col1xml.value('/x[2]','varchar(max)') column1_2        ,col1xml.value('/x[3]','varchar(max)') column1_3        ,col2xml.value('/x[1]','varchar(max)') column2_1       ,col2xml.value('/x[2]','varchar(max)') column2_2        ,col2xml.value('/x[3]','varchar(max)') column2_3   splitted