首页 > 数据库技术 > 详细

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

时间:2018-01-17 14:16:31      阅读:336      评论:0      收藏:0      [点我收藏+]

原数据

技术分享图片

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

技术分享图片

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

 

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

原文:https://www.cnblogs.com/LessIsMoreZ/p/8302536.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!