22,300
社区成员




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
这是在各字段不填时为空串时的写法,如果为NULL,则:select *,isnull(字3,'')+','+isnull(字2,'')+','+isnull(字1,'') as 描述 from 表A
select *,字3+','+字2+','+字1 as 描述 from 表A