請參見:--函數,合並字符串欄位
--測試數據
/*
CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10))
INSERT INTO person_info
SELECT '資訊部','軟件編碼員','小李'
UNION ALL SELECT '資訊部','軟件編碼員','小王'
UNION ALL SELECT '行政部','秘書','小芳'
*/
--合並函數
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB
RETURN(substring(@r,2,8000))
END
GO
--删除测试
DROP TABLE person_info
DROP FUNCTION FunMergeCharField--調用
SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name)
FROM person_info
GROUP BY dept_name,position_name
go
--測試數據
/*
CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10))
INSERT INTO person_info
SELECT '資訊部','軟件編碼員','小李'
UNION ALL SELECT '資訊部','軟件編碼員','小王'
UNION ALL SELECT '行政部','秘書','小芳'
*/
--合並函數
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB
RETURN(substring(@r,2,8000))
END
GO
--删除测试
DROP TABLE person_info
DROP FUNCTION FunMergeCharField--調用
SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name)
FROM person_info
GROUP BY dept_name,position_name
go
create function f_merg(@type char(1))
returns varchar(20)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+name from 表 where type=@type
return(substring(@r,2,8000))
end
go--调用
select dbo.f_merg('a'),dbo.f_merg('b'),dbo.f_merg('c')--测试--测试数据
create table 表(ID int,Type char(1),Name varchar(10),Code int)
insert 表 select 1,'A','Computer',101
union all select 2,'A','Mobile',102
union all select 3,'B','Desk',201
union all select 4,'B','Box',202
union all select 5,'C','Fan',301
go--创建处理函数
create function f_merg(@type char(1))
returns varchar(20)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+name from 表 where type=@type
return(substring(@r,2,8000))
end
go--调用
select dbo.f_merg('a'),dbo.f_merg('b'),dbo.f_merg('c')
go--删除测试
drop table 表
drop function f_merg/*--测试结果
-------------------- -------------------- --------------------
Computer,Mobile Desk,Box Fan(所影响的行数为 1 行)--*/
insert into @t
select 99,'A' union select
99,'A,B' union select
99,'B,C'
declare @str varchar(8000)set @str=''
select @str=@str+','+[name] from @t where titleid=99
select @str=right(@str,len(@str)-1)/*下面部分可以寫成函數*/
declare @i int
declare @str1 varchar(800)
,@str3 varchar(20)
select @i=1
while @i<=len(@str)
begin
set @str3=substring(@str,@i,2)
if @i=1
set @str1=@str3
else if(charindex(@str3,@str1,1)=0)
set @str1=@str1+@str3
set @i=@i+2
end
select @str1
insert into bbc values(99,'A')
insert into bbc values(99,'A,B')
insert into bbc values(99,'B,C,E,F')
insert into bbc values(99,'mao,E,F,BC')
--查詢調用函數
select a,dbo.get_difficute(a) as Name from bbc group by a order by a
--結果
--a Name--99 A,B,C,E,F,mao,BC--這樣寫一個函數
create function get_difficute(@a int)
returns varchar(400)
as
begin
Declare @sss varchar(400),@b varchar(300)
set @sss = ''
select @sss = @sss + ','+ b from bbc where a = @a order by b
set @sss = right(@sss,len(@sss) -1)
set @b =''
while charindex(',',@sss) > 0
begin
if charindex(left(@sss,charindex(',',@sss)),@b) <= 0
set @b = @b + left(@sss,charindex(',',@sss))
set @sss = substring(@sss,charindex(',',@sss)+1,len(@sss))
end
set @b = @b + @sss
return @b
end
create table bbc (a int,b varchar(20))
insert into bbc values(99,'A')
insert into bbc values(99,'A,B')
insert into bbc values(99,'B,C,E,F')
insert into bbc values(99,'mao,E,F,BC')
insert into bbc values(99,'mao,E,F,BC')
insert into bbc values(99,'mao,E,F,BC')
insert into bbc values(99,'mao,E,F,BC')