The SQL statement splits two columns of data into more than one

Original data

You want to split the two columns into:

SQL statements and ideas

Idea: Split column A and column B separately, with line numbers equal as conditional connections

SelectManagerid,manager from(SELECT row_number () over (ORDER by B.number) asOrderid,substring (Prjmanager,b.number,charindex (',', prjmanager+',', B.number)-B.number) as Manager from [dbo]. [Cm_contract] T1 INNER join master.dbo.spt_values B on b.number between1and Len (Prjmanager) and substring (','+prjmanager,b.number,1)=','   whereB.type='P') asT1 INNER JOIN (SELECT row_number () over (ORDER by Cntname) asOrderid,substring (Prjmanagerids,b.number,charindex (',', prjmanagerids+',', B.number)-B.number) as ManagerID from [dbo]. [Cm_contract] T1 INNER join master.dbo.spt_values B on b.number between1and Len (prjmanagerids) and substring (','+prjmanagerids,b.number,1)=','  whereB.type='P') asT2 on T1.orderid=t2.orderid

The SQL statement splits two columns of data into more than one