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 #tbDROP TABLE #tempDROP TABLE #resultDROP TABLE #resultend

<pre class="sql" name="code">SELECT * FROM#resultenddeclare @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.a2FROM (SELECT SerialNo AS a1 ,ConsultorID AS a2 FROM @t)AS aWHERE dbo.tblLatentStudent.SerialNo=a.a1SELECT ConsultorID,Consultor,* FROM tblLatentStudent           drop table #resultend