sqlserver：表中一列为分隔符分割的多个人名，添加一列存储每个人名的id用逗号分割

表中的数据是历史数据，ConsultorI列是人名的拼接，表中原本没有存储对应人名的id列ConsultorID，现添加一列ConsultorID，如图所示

对应的人名的id表是tblMember如图

现在问题来了，怎么用sqlserver把Consultor中存储的人名转换为对应的id，并用逗号分割存储到ConsultorID中呢？

因为sqlserver没有数组的概念

我的思路是：

 

--创建临时表放tblLatentStudent中的信息
CREATE TABLE #tb
    (
      SerialNo INT ,
      Consultor  VARCHAR(MAX) ,
      culomnsxml XML DEFAULT NULL
    )

--中间临时表存放SerialNo和切割后的Consultor字段
CREATE TABLE #temp
    (
      SerialNo INT ,
      Consultor VARCHAR(MAX) 
    )
--中间临时表存放tblMember表中咨询老师id和咨询老师名name
CREATE TABLE #result
    (
      id INT ,
      name VARCHAR(50) 
    )
    --最终表存放学生号SerialNo,和对应老师的名字Consultor和id (ConsultorID)
    CREATE TABLE #resultend
    (
     SerialNo INT ,
      Consultor VARCHAR(100) ,
      ConsultorID VARCHAR(50) 
    )
    
 insert into #tb(SerialNo,Consultor) select SerialNo,Consultor from tblLatentStudent



UPDATE  #tb
SET     culomnsxml = CONVERT(XML , '<root><v>' + REPLACE(a.Consultor , '/' , '</v><v>') + '</v></root>')
FROM    #tb a
SELECT SerialNo , Consultor,culomnsxml FROM #tb

INSERT  INTO #temp ( SerialNo , Consultor )
        SELECT  SerialNo , cx
        FROM    #tb a
                CROSS APPLY ( SELECT C.v.value ('.' , 'nvarchar(100)') cx FROM a.culomnsxml.nodes ('/root/v') C( v)
                            ) b   
--删除Consultor为空的行
DELETE FROM #temp WHERE Consultor=''
--插入表 tblMember的id和名字     
 INSERT  INTO #result ( id,name )
        (SELECT id,name
        FROM    tblMember   )  
         SELECT* FROM #result
INSERT  INTO #resultend ( SerialNo,Consultor,ConsultorID )(
        SELECT a.SerialNo,a.Consultor,b.id
        FROM    #temp a
    -- INNER   JOIN #result b ON a.Consultor=b.name
   LEFT JOIN #result b ON a.Consultor=b.name
   )
SELECT  *
FROM    #resultend  

DROP TABLE #tb
DROP TABLE #temp
DROP TABLE #result
DROP TABLE #resultend

<pre class="sql" name="code">SELECT * FROM	#resultend
declare @t table(SerialNo int,ConsultorID varchar(50))--定义结果集表变量   
--定义游标并进行合并处理  
 declare my_cursor cursor local for 
 select SerialNo , ConsultorID from #resultend  
 declare @id_old int , @id int , @value varchar(10) , @s varchar(100)  
 open my_cursor  
 fetch my_cursor into @id , @value  
 select @id_old = @id , @s=''  
 while @@FETCH_STATUS = 0  
begin  
  if @id = @id_old  
   select @s = @s + ',' + cast(@value as varchar)
     else  
        begin  
             insert @t values(@id_old , stuff(@s,1,1,''))  
     select @s = ',' + cast(@value as varchar) , @id_old = @id 
         end  
           fetch my_cursor into @id , @value  
         END  
         insert @t values(@id_old , stuff(@s,1,1,''))  
         close my_cursor  
         deallocate my_cursor  
          select * from @t 
UPDATE dbo.tblLatentStudent SET ConsultorID=a.a2
FROM (SELECT SerialNo AS a1 ,ConsultorID AS a2 FROM @t)AS a
WHERE dbo.tblLatentStudent.SerialNo=a.a1
SELECT ConsultorID,Consultor,* FROM tblLatentStudent 
          drop table #resultend