最简单的方法

create table #TryKK(名称 nvarchar(10), 字1 nvarchar(10), 字2  nvarchar(10), 字3 nvarchar(10), 描述 nvarchar(50))

insert #TryKK select '张三','','','',' A1,B2,C3' union all

select '李四' ,'','','','A1,B2,D4' union all

select '王五','','','','E5,C3'



alter function [dbo].[m_split](@c varchar(2000),@split varchar(2))  --把字符串截開，生成一個單列的表 

  returns @t table(id int,col varchar(200))   

  as   

    begin   

      declare @i as int

      set @i=1

      while(charindex(@split,@c)<>0)   

        begin   

          insert @t(id,col) values (@i,substring(@c,1,charindex(@split,@c)-1))   

            set @c = stuff(@c,1,charindex(@split,@c),'')   

            set @i=@i+1

        end   

      insert @t(id,col) values (@i,@c)   

      return   

end



select 名称,字1=(select col from [dbo].[m_split](描述,',') where id=(select max(id) from [dbo].[m_split](描述,',')))

,字2=(select col from [dbo].[m_split](描述,',') where id=(select max(id) from [dbo].[m_split](描述,','))-1)

,字3=(select col from [dbo].[m_split](描述,',') where id=(select max(id) from [dbo].[m_split](描述,','))-2)

,描述

from #TryKK

create table 表A(名称 nvarchar(10),字1 nvarchar(10),字2 nvarchar(10),字3 nvarchar(10),描述 nvarchar(30))

insert into 表A(名称,描述) select '张三','A1,B2,C3'

insert into 表A(名称,描述) select '李四','A1,B2,D4'

insert into 表A(名称,描述) select '王五','E5,C3'

go

;with c1 as(

select 名称,(case when len(replace(描述,',',''))=len(描述)-1 then ' ,'+描述 else 描述 end) as tmp from 表A

),c2 as(

select 名称,left(tmp,charindex(',',tmp)-1) as z1,right(tmp,len(tmp)-charindex(',',tmp))tmp from c1

),c3 as(

select 名称,z1,left(tmp,charindex(',',tmp)-1) as z2,right(tmp,len(tmp)-charindex(',',tmp))tmp from c2

) 

update 表A set 字1=b.tmp,字2=b.z2,字3=b.z1 from 表A a inner join c3 b on a.名称=b.名称

go

select * from 表A

/*

名称         字1         字2         字3         描述

---------- ---------- ---------- ---------- ------------------------------

张三         C3         B2         A1         A1,B2,C3

李四         D4         B2         A1         A1,B2,D4

王五         C3         E5                    E5,C3



(3 行受影响)



*/

go

drop table 表A

create table 表A(名称 nvarchar(10),字1 nvarchar(10),字2 nvarchar(10),字3 nvarchar(10),描述 nvarchar(30))

insert into 表A(名称,字1,字2,字3) select '张三','A1','B2','C3'

insert into 表A(名称,字1,字2,字3) select '李四','A1','B2','D4'

insert into 表A(名称,字1,字2) select '王五','E5','C3'

go

update 表A set 描述=isnull(字3+',','')+isnull(字2+',','')+isnull(字1,'')

select * from 表A

/*

名称         字1         字2         字3         描述

---------- ---------- ---------- ---------- ------------------------------

张三         A1         B2         C3         C3,B2,A1

李四         A1         B2         D4         D4,B2,A1

王五         E5         C3         NULL       C3,E5



(3 行受影响)

*/

go

drop table 表A

update 表A set 描述=isnull(字3,'')+','+isnull(字2,'')+','+isnull(字1,'')

select *,字3+','+字2+','+字1 as 描述 from 表A

select *,isnull(字3,'')+','+isnull(字2,'')+','+isnull(字1,'') as 描述 from 表A

select *,字3+','+字2+','+字1 as 描述 from 表A