--建立测试环境
Create Table 表(id varchar(10),productname varchar(10),producttype varchar(10),productnum varchar(10))
--插入数据
insert into 表
select '1','aaaa','a_type1','1000200' union
select '2','aaaa','a_type2','1000201' union
select '3','bbb','b_type1','1000300' union
select '4','bbb','b_type2','1000201' union
select '5','bbb','b_type3','1000203'
--select * from 表
--测试语句go
CREATE FUNCTION FunMergeCharField1(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+producttype FROM 表 WHERE productname=@vchA
RETURN(substring(@r,2,8000))
END
go
CREATE FUNCTION FunMergeCharField2(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+productnum FROM 表 WHERE productname=@vchA
RETURN(substring(@r,2,8000))
END
goselect productname, producttypes=dbo.FunMergeCharField1(productname),
productnums=dbo.FunMergeCharField2(productname)
from 表 group by productname
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField1
Drop FUNCTION FunMergeCharField2
/*
productname producttypes productnums
----------- ----------------------- -----------------
aaaa a_type1,a_type2 1000200,1000201
bbb b_type1,b_type2,b_type3 1000300,1000201,1000203
*/
Create Table 表(id varchar(10),productname varchar(10),producttype varchar(10),productnum varchar(10))
--插入数据
insert into 表
select '1','aaaa','a_type1','1000200' union
select '2','aaaa','a_type2','1000201' union
select '3','bbb','b_type1','1000300' union
select '4','bbb','b_type2','1000201' union
select '5','bbb','b_type3','1000203'
--select * from 表
--测试语句go
CREATE FUNCTION FunMergeCharField1(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+producttype FROM 表 WHERE productname=@vchA
RETURN(substring(@r,2,8000))
END
go
CREATE FUNCTION FunMergeCharField2(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+productnum FROM 表 WHERE productname=@vchA
RETURN(substring(@r,2,8000))
END
goselect productname, producttypes=dbo.FunMergeCharField1(productname),
productnums=dbo.FunMergeCharField2(productname)
from 表 group by productname
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField1
Drop FUNCTION FunMergeCharField2
/*
productname producttypes productnums
----------- ----------------------- -----------------
aaaa a_type1,a_type2 1000200,1000201
bbb b_type1,b_type2,b_type3 1000300,1000201,1000203
*/
id int,
productname varchar(10),
producttype varchar(10),
productnum varchar(10))insert into t select 1,'aaaa','a_type1','1000200'
insert into t select 2,'aaaa','a_type2','1000201'
insert into t select 3,'bbb ','b_type1','1000300'
insert into t select 4,'bbb ','b_type2','1000201'
insert into t select 5,'bbb ','b_type3','1000203'create function func_str_sum(@typeId int,@productname varchar(10))
returns varchar(8000)
as
begin
declare @sums varchar(8000)
set @sums=''
select @sums=@sums+','+case @typeId when 1 then rtrim(producttype) when 2 then rtrim(productnum) end
from t where productname=@productname
return(stuff(@sums,1,1,''))
endselect
productname,
dbo.func_str_sum(1,productname),
dbo.func_str_sum(2,productname)
from
t
group by
productname
create table t(
id int,
productname varchar(10),
producttype varchar(10),
productnum varchar(10))insert into t select 1,'aaaa','a_type1','1000200'
insert into t select 2,'aaaa','a_type2','1000201'
insert into t select 3,'bbb ','b_type1','1000300'
insert into t select 4,'bbb ','b_type2','1000201'
insert into t select 5,'bbb ','b_type3','1000203'--创建自定义函数
--@typeId = 1:汇总producttype
--@typeId = 2:汇总productnumcreate function func_str_sum1(@typeId int,@productname varchar(10))
returns varchar(8000)
as
begin
declare @sums varchar(8000)
set @sums=''
select @sums=@sums+','+case @typeId when 1 then rtrim(producttype) when 2 then rtrim(productnum) end
from t where productname=@productname
return(stuff(@sums,1,1,''))
end--执行查询,调用自定义函数
select
productname,
producttype = dbo.func_str_sum1(1,productname),
productnum = dbo.func_str_sum1(2,productname)
from
t
group by
productname
returns varchar(8000)
as
begin
declare @sums varchar(8000)
set @sums=''
select @sums=@sums+','+case @typeId when 1 then rtrim(producttype) when 2 then rtrim(productnum) end
from t where productname=@productname
return(stuff(@sums,1,1,''))
end
returns varchar(1000)
begin
declare @str varchar(1000)
set @str=''
select @str=@str+productnums from a where
returns @str
end
go
select productname, producttypes=dbo.sumstr(producttype)
from a group by productname
rfq(任凤泉) 不好意思。我结完帐才看到你的回复。。多谢你的热情。。给分如下:
wyb0026(小小) 5
jinjazz(近身剪*10年磨一贴):35
libin_ftsafe(子陌红尘) :40
xluzhong(打麻将一缺三,咋办?) :20多谢各位特别是jinjazz(近身剪*10年磨一贴)和libin_ftsafe(子陌红尘) 都给了测试数据