sql语句之拆分两列数据为多条
标签：sub 分享图片 color src info .com div from body
sql语句之拆分两列数据为多条
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