sql - Make a separate row respectively if a column contains comma-separated value -

i asked question here bit change 1

firstname          lastname                     payscale  -----------        ------------                --------------- alice,lisa         simons,jack                        100000 

i want see result

   firstname          lastname                     payscale   -----------        ------------                --------------- alice              simons                        100000 lisa               jack                          100000 

first value of firstname , first value of lastname make seperate row . in short want comma separate multiple columns

as said garethd should normalize database. workaround

create table #test (firstname varchar(100),lastname varchar(100),payscale int)  insert #test values ('alice,lisa','simons,jack',100000)  select firstname,        lastname,        b.payscale   (select split.a.value('.', 'varchar(100)')firstname,                payscale,                row_number()                  over(                    order (select 1))          rn           (select payscale,                        cast ('<m>' + replace(firstname, ',', '</m><m>')                              + '</m>' xml) firstname                   #test)                cross apply firstname.nodes ('/m') split(a))fst        join (select split.a.value('.', 'varchar(100)') lastname,                     payscale,                     row_number()                       over(                         order (select 1))           rn                (select payscale,                             cast ('<m>' + replace(lastname, ',', '</m><m>')                                   + '</m>' xml) lastname                        #test)                     cross apply lastname.nodes ('/m') split(a)) b          on fst.rn = b.rn