November 9, 2006 at 3:38 am
hello everyone,in sqlserver 2000
i have a query:
SELECT dbo.SystemRoles.RoleName, dbo.SystemRolesPermissions.IsEnabled, dbo.SystemRolesPermissions.ActionID,
dbo.ApplicationModules.ModuleName
FROM dbo.SystemRoles INNER JOIN
dbo.SystemRolesPermissions ON dbo.SystemRoles.SystemRoleID = dbo.SystemRolesPermissions.SystemRoleID INNER JOIN
dbo.ModuleActions ON dbo.SystemRolesPermissions.ActionID = dbo.ModuleActions.ActionID INNER JOIN
dbo.ApplicationModules ON dbo.ModuleActions.ModuleID = dbo.ApplicationModules.ModuleID
this query retruns the result in this format :
RoleName IsEnabled ActionID ModuleName
Admin 1 1 ManageBuyers
Admin 1 2 ManageSuppliers
Admin 1 2 ManagePins
Admin 1 2 ManageTest
Operator 0 3 ManageBuyers
Guest 0 4 ManageBuyers
Guest 0 4 ManageSuppliers
Guest 0 4 ManagePins
Now i want my query returns the data in the follwoing format
ModuleName
RoleName ManageBuyers ManageSuppliers ManagePins ManageTest
Admin 1 1 1 1
Operator 1 0 0 0
Guest 1 1 1 0
plz tell me how i achieve this , its very urgent plz give me some idea how can i implement this thing
(This is just like to show which users access which kind of roles
November 9, 2006 at 3:47 am
Pivoting data in SQL Server 2000 can be done, but it's clumsy and cumbsersome. Search this site - there are plenty of answers to this sort of problem.
What I'd do is export the data into a spreadsheet and use the versatile pivoting GUI in Excel.
John
November 10, 2006 at 8:30 am
Quick, simple, down and dirty based on what you have revealed about the data.
SELECT SR.RoleName,
'ManageBuyers' = MAX(CASE AM.ModuleName WHEN 'ManageBuyers' THEN 1 ELSE 0 END),
'ManageSuppliers' = MAX(CASE AM.ModuleName WHEN 'ManageSuppliers' THEN 1 ELSE 0 END),
'ManagePins' = MAX(CASE AM.ModuleName WHEN 'ManagePins' THEN 1 ELSE 0 END),
'ManageTest' = MAX(CASE AM.ModuleName WHEN 'ManageTest' THEN 1 ELSE 0 END)
FROM dbo.SystemRoles SR
INNER JOIN dbo.SystemRolesPermissions SRP ON SR.SystemRoleID = SRP.SystemRoleID
INNER JOIN dbo.ModuleActions MA ON SRP.ActionID = MA.ActionID
INNER JOIN dbo.ApplicationModules AM ON MA.ModuleID = AM.ModuleID
GROUP BY SR.RoleName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply