CREATE FUNCTION GETSTR(@ID NVARCHAR(20))
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @str NVARCHAR(200)
SET @str='' SELECT @str = @str+text+','
FROM mydata WHERE Uid =@ID SET @str = LEFT(@str,LEN(@str)-1)
RETURN @str
END然后用下面语句就可以了
select uid,max(name),dbo.getstr(uid) from mydata group by uid
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @str NVARCHAR(200)
SET @str='' SELECT @str = @str+text+','
FROM mydata WHERE Uid =@ID SET @str = LEFT(@str,LEN(@str)-1)
RETURN @str
END然后用下面语句就可以了
select uid,max(name),dbo.getstr(uid) from mydata group by uid
set @sql=''
select id,name,@sql=@sql+','
from mydata
group by id,name
RETURNS nvarchar(3000)
AS
BEGIN
declare @sumstr nvarchar(3000)
set @sumstr=''
select @sumstr=@sumstr+case when @sumstr<>'' then ',' else '' end+[text] from mydata where name=@id
return(@sumstr)
END
GO
select uid,name,dbo.sumstr(name) as [text] from mydata group by uid,name
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+text+',' from mydata where uid=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end--执行
select distinct uid,name,dbo.test(uid) as asdf from mydata