sql语句之拆分两列数据为多条

原数据

想要把两列进行拆分变成：

sql语句及思路

思路：先分别拆分A列和B列，以行号相等作为条件连接

select ManagerId,Manager from 
    (SELECT ROW_NUMBER() OVER(ORDER BY b.number) as orderId,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 between 1 and len(PrjManager)
    and substring(','+PrjManager,b.number,1)=','
   where b.type='P') as t1
  inner join 
(SELECT ROW_NUMBER() OVER(ORDER BY cntName) as orderId,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 between 1 and len(PrjManagerIds)
    and substring(','+PrjManagerIds,b.number,1)=','
  where b.type='P') as t2 ON t1.orderId=t2.orderId

 

转载于:https://www.cnblogs.com/LessIsMoreZ/p/8302536.html