--1.创建一个合并的函数 create function fmerg(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+','+cast(PermissionID as varchar) from 表A where userid=@id set @str=right(@str,len(@str)-1) return(@str) End go--调用自定义函数得到结果 select distinct userid,dbo.fmerg(userid) from 表A
--用临时表的方法,也适用于sql7.0 select *,a=cast('' as varchar(8000)) into #t order by userid declare @id int,@a varchar(8000)update #t set @a=case @id when userid then @a+','+cast(PermissionID as varchar) else cast(PermissionID as varchar) end ,a=@a,@id=userid select userID,PermissionID=max(a) from #t group by userid
谢谢各位的帮忙! 问题搞好了!CREATE PROCEDURE usp_Accounts_GetEffectivePermissions @UserID int, @Permissions varchar(200) OUTPUT AS SET @Permissions = '' SELECT ur.UserID, @Permissions = @Permissions + ',' + CAST(rp.PermissionID AS varchar) FROM Accounts_UserRoles AS ur INNER JOIN Accounts_Roles AS r ON (ur.RoleID = r.RoleID) AND ur.UserID = @UserID INNER JOIN Accounts_RolePermissions AS rp ON rp.RoleID = ur.RoleID ORDER BY rp.PermissionID ASC SET @Permissions = RIGHT(@Permissions, LEN(@Permissions) - 1) + ',' GO
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(PermissionID as varchar) from 表A where userid=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct userid,dbo.fmerg(userid) from 表A
--用临时表的方法,也适用于sql7.0
select *,a=cast('' as varchar(8000)) into #t order by userid
declare @id int,@a varchar(8000)update #t set @a=case @id when userid then @a+','+cast(PermissionID as varchar)
else cast(PermissionID as varchar) end
,a=@a,@id=userid
select userID,PermissionID=max(a) from #t group by userid
@UserID int,
@Permissions varchar(200) OUTPUT
AS
SET @Permissions = '' SELECT
ur.UserID,
@Permissions = @Permissions + ',' + CAST(rp.PermissionID AS varchar)
FROM Accounts_UserRoles AS ur
INNER JOIN Accounts_Roles AS r
ON (ur.RoleID = r.RoleID) AND ur.UserID = @UserID
INNER JOIN Accounts_RolePermissions AS rp
ON rp.RoleID = ur.RoleID
ORDER BY rp.PermissionID ASC SET @Permissions = RIGHT(@Permissions, LEN(@Permissions) - 1) + ','
GO