CREATE table #t (name varchar(8000), app int)
Insert #t (app) Select app from 表 Group by app
declare @name varchar(8000), @max int @min int, @app int
select @min = min(id), @max = max(id)
while @min <= @max
begin
Select @name = name, @app = app from 表 where id = @min
update name = name +@name From #t where app = @app
set @min = @min + 1
end
Select * From #t
drop table #t
Insert #t (app) Select app from 表 Group by app
declare @name varchar(8000), @max int @min int, @app int
select @min = min(id), @max = max(id)
while @min <= @max
begin
Select @name = name, @app = app from 表 where id = @min
update name = name +@name From #t where app = @app
set @min = @min + 1
end
Select * From #t
drop table #t
Insert #t (app) Select app from 表 Group by app
declare @name varchar(8000), @max int @min int, @app int, @name1 varchar(8000)
select @min = min(id), @max = max(id)
while @min <= @max
begin
Select @name = name, @app = app from 表 where id = @min
Select @name1 = name From #t Where app = @app
if @name1 = ''
begin
Update name = @name From #t where app = @app
end
else
begin
update name = name + ',' + @name From #t where app = @app
end
set @min = @min + 1
end
Select * From #t
drop table #t
create function f_1(@app VARCHAR(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+name from t where app=@app
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct dbo.f_1(app) name,app from t
select 1,'ding',11
union all
select 2,'wang',22
union all
select 3,'zhan',11
union all
select 4,'zhang',22
union all
select 5,'qiu',11select dbo.f_1(app),app from a
group by appalter function f_1(@app VARCHAR(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+name from a where app=@app
set @str=right(@str,len(@str)-1)
return(@str)
End
go
name app
,ding,zhan 11
,wang,zhang,qiu 22
????????
太棒了!非常感谢。
不过我遇到了点小问题(不是你程序的问题),提醒后面要用的朋友。如name为int型,合并函数为:
create function f_1(@app VARCHAR(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+convert( varchar(20),name) from t where app=@app
set @str=right(@str,len(@str)-1)
return(@str)
End
go