create function f_str(@id int,@f1 varchar(10),@id1 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+f2
from T1
where id=@id and f1=@f1 and id1=@id1
return stuff(@str,1,1,'')
end
go--查询
select id
,f1
,id1
,dbo.f_str(id,f1,id1)
from T1
group by id,f1,id1
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+f2
from T1
where id=@id and f1=@f1 and id1=@id1
return stuff(@str,1,1,'')
end
go--查询
select id
,f1
,id1
,dbo.f_str(id,f1,id1)
from T1
group by id,f1,id1
--参考
有表內容﹕
編號 內容
A abc
A aaa
A dddd
B 1223
B fkdjfd
....實現結果﹕
A abc,aaa,dddd
B 1223,fkdjfd
要求用一條SQL實現﹐如﹕select sum(內容) from table group by 編號--该问题,写一个合并函数,后,分组合并既可!--测试数据
create Table 表(編號 varchar(20),內容 varchar(20))
insert 表 select 'A','abc'
union all select 'A','aaa'
union all select 'A','dddd'
union all select 'B','1223'
union all select 'B','fkdjfd'
--处理分组合并函数学
Create Function JoinStr(@SNO as varchar(20))
returns varchar(200)
begin
declare @s as varchar(8000)
set @s=''
select @s=@s+','+ltrim(rtrim(內容)) from
(
select 內容 from 表 where 編號=@SNO
)
A
set @s=stuff(@s,1,1,'')
return @s
end--查询语句
select 編號,dbo.JoinStr(編號) as 内容 from 表 group by 編號--测试结果:
編號 内容
A abc,aaa,dddd
B 1223,fkdjfd
---------------------------------
1 A
2 B
1 C
3 E
2 F
1 G?果
name value
-----------------------
1 A,C,G
2 B,F
3 E--生成??数据
create table tname(name int,value varchar(10))
insert into tname select 1,'A'
insert into tname select 2,'B'
insert into tname select 1,'C'
insert into tname select 3,'E'
insert into tname select 2,'F'
insert into tname select 1,'G'
go--?建用?定?函数
create function f_str(@name int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + value from tname where name = @name
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--?行??
select name,value=dbo.f_str(name) from tname group by name order by name--?出?果
/*
name value
---- -----
1 A,C,G
2 B,F
3 E
*/--?除???境
drop function f_str
drop table tname
go
(
id int,f1 varchar(10),id1 int,f2 varchar(10)
)
insert T1
select 1,'A',1,'A1' union
select 1,'A',1,'A2' union
select 1,'A',1,'A3' union
select 2,'B',2,'B1' union
select 2,'B',2,'B2' union
select 2,'B',2,'B3' union
select 3,'C',3,'C1' union
select 3,'C',3,'C2'
gocreate function f_str(@id int,@f1 varchar(10),@id1 int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+f2
from T1
where id=@id and f1=@f1 and id1=@id1
return stuff(@str,1,1,'')
end
go--查询
select id
,f1
,id1
,dbo.f_str(id,f1,id1) as 'f2'
from T1
group by id,f1,id1--删除测试环境
drop function f_str
drop table T1--结果
/*
id f1 id1 f2
----------- ---------- ----------- --------------
1 A 1 A1,A2,A3
2 B 2 B1,B2,B3
3 C 3 C1,C2(所影响的行数为 3 行)
*/
--处理的数据
CREATE TABLE t2(f2 varchar(10),id1 int)
INSERT t2 SELECT 'A1',1
UNION ALL SELECT 'A2',1
UNION ALL SELECT 'A3',1
UNION ALL SELECT 'B1',2
UNION ALL SELECT 'B2',2
UNION ALL SELECT 'B3',2
UNION ALL SELECT 'C1',3
UNION ALL SELECT 'C2',3
UNION ALL SELECT 'C3',3
GO--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(f2 as varchar)
FROM T2
WHERE id1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT id1,f2=dbo.f_str(id1) FROM t2 GROUP BY id1
--删除测试
DROP TABLE t2
DROP FUNCTION f_str
/*--结果
id1 col2
---------- -----------
1 A1,A2,A3
2 B1,B2,B3
3 C1,C2,C3
--*/
GO/*==============================================*/