--字符号串分拆(表中)
****************************
--创建拆分函数
--创建辅助表
if exists(select 1 from sysobjects where id=object_id(N'[dbo].[tep]')
and objectproperty(id,N'IsUserTable')=1)
drop table tep
go
select top 8000 id=identity(int,1,1) into tep
from syscolumns a,syscolumns b
alter table tep add constraint pk_id_tep primary key(id)
go
if exists(select 1 from sysobjects where id=object_id(N'[dbo].[f_split]')
and xtype in (N'FN',N'IF',N'TF'))
drop function f_split
go
create function f_split(
@str varchar(8000), --要分拆的字符串
@splitchar varchar(10) --分隔符
)returns table
as
return(
select re=substring(@str,id,charindex(@splitchar,@str+@splitchar,id)-id)
from tep
where substring(@splitchar+@str,id,len(@splitchar))=@splitchar
)
go
--字符号串合并函数
--***********************************************************************
--建立测试表
if objectproperty(object_id(N'[dbo].[table1]'),N'IsUserTable')=1
drop table table1
go
create table table1(field1 char(10),field2 char(10))
--加入测试数据
insert into table1 values('项目一','a')
insert into table1 values('项目一','b')
insert into table1 values('项目一','c')
insert into table1 values('项目一','d')
--创建合并函数
if exists(select 1 from sysobjects where id=object_id(N'[dbo].[UniteStr]')
and xtype in (N'FN',N'IF',N'TF'))
drop function UniteStr
go
create function UniteStr(@condition1 varchar(10))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=rtrim(ltrim(@r))+','+rtrim(ltrim(field2)) from table1 where field1=@condition1
return(substring(@r,2,8000))
end
--SQL:
select field1,field2=dbo.UniteStr(field1) from table1 group by field1
--字符号串分拆(表中)
--*****************************************************************************
--建立测试表,加入测试数据
if objectproperty(object_id(N'[dbo].[table1]'),N'IsUserTable')=1
drop table table1
go
create table table1(field1 char(10),field2 char(10))
insert into table1 values('项目一','a,b,c,d')
insert into table1 values('项目二','ab,dd,ce')
--创建辅助表
if exists(select 1 from sysobjects where id=object_id(N'[dbo].[tep]')
and objectproperty(id,N'IsUserTable')=1)
drop table tep
go
select top 8000 id=identity(int,1,1) into tep
from syscolumns a,syscolumns b
alter table tep add constraint pk_id_tep primary key(id)
go
--SQL
select a.field1,field2=substring(a.field2,b.id,charindex(',',a.field2+',',b.id)-b.id)
from table1 a,tep b
where substring(','+a.field2,id,len(','))=','
order by field1
--*******************************************************************************
完全同意马可的意见只是需要添加几个去空格的就更好了.
create function fmerg(@id varchar(3))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+ 员工培训 from 表A where rtrim(员工id)=rtrim(@id)
return(@str)
End
go
--调用自定义函数得到结果
select distinct 员工id,dbo.fmerg(员工id) from 表A
--1.创建一个合并的函数
create function fmerg(@id varchar(3))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+ 员工培训 from 表A where 员工id=@id
return(@str)
End
go
--调用自定义函数得到结果
select distinct 员工id,dbo.fmerg(员工id) from 表A