if object_id('tbTest') is not null drop table tbTest if object_id('fnStringJoin') is not null drop function fnStringJoin GO create table tbTest(c1 int, c2 varchar(20)) insert tbTest select 1, 'A' union all select 1, 'B' union all select 2, 'C' union all select 2, 'D' union all select 2, 'E' union all select 3, 'F' GO ----创建字符串连接函数 create function fnStringJoin(@id int) returns varchar(1000) as begin declare @str varchar(1000) set @str = '' select @str = @str + ',' + c2 from tbTest where c1 = @id return stuff(@str,1,1,'') end GO ----查询 select distinct c1,c2 = dbo.fnStringJoin(c1) from tbTest----清除测试环境 drop table tbTest drop function fnStringJoin/*结果 c1 c2 1 A,B 2 C,D,E 3 F */
create table tb(c1 int,c2 varchar(100)) go insert into tb select 1,'a' union all select 1,'b' union all select 2,'c' union all select 3,'d' union all select 3,'e' union all select 3,'f'go --写一个聚合函数: create function dbo.fn_Merge(@c1 int) returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+','+c2 from tb where c1=@c1 return stuff(@r,1,1,'') end go-- 调用函数 select c1, dbo.fn_Merge(c1) as txt from tb group by c1
drop table tbTest
if object_id('fnStringJoin') is not null
drop function fnStringJoin
GO
create table tbTest(c1 int, c2 varchar(20))
insert tbTest
select 1, 'A' union all
select 1, 'B' union all
select 2, 'C' union all
select 2, 'D' union all
select 2, 'E' union all
select 3, 'F'
GO
----创建字符串连接函数
create function fnStringJoin(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + c2 from tbTest where c1 = @id
return stuff(@str,1,1,'')
end
GO
----查询
select distinct c1,c2 = dbo.fnStringJoin(c1) from tbTest----清除测试环境
drop table tbTest
drop function fnStringJoin/*结果
c1 c2
1 A,B
2 C,D,E
3 F
*/
create table tb(c1 int,c2 varchar(100))
go
insert into tb
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 3,'d' union all
select 3,'e' union all
select 3,'f'go
--写一个聚合函数:
create function dbo.fn_Merge(@c1 int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+c2 from tb where c1=@c1
return stuff(@r,1,1,'')
end
go-- 调用函数
select c1, dbo.fn_Merge(c1) as txt from tb group by c1
go
drop table tb
drop function fn_Merge