March 24, 2015 at 4:40 am
I have to create a query that will be returning the users and the application modules they have access for. Along with the list of users and modules the query should also return if any module is not accessible for that user.
The 'ApplicationUsers' table is like this:
CREATE TABLE #ApplicationUsers
(
userId INT,
UserName VARCHAR(50)
)
INSERT INTO #ApplicationUsers VALUES
(1, 'Daniel'), (2,'Martin'), (3, 'Brandon'), (4, 'Doug')
The 'ApplicationModule' table is like this:
CREATE TABLE #ApplicationModules
(
moduleId INT,
moduleName VARCHAR(50)
)
INSERT INTO #ApplicationModules VALUES
(1, 'Sales'), (2, 'Production'), (3, 'Marketing')
This is how we store module access for users in 'ModuleAccess' table:
CREATE TABLE #ModuleAccess
(
Id INT,
userId INT,
moduleId INT
)
INSERT INTO #ModuleAccess VALUES
(1, 1, 1), (2, 1, 3), (3, 2, 1), (4, 2, 2), (5, 3, 2)
In the example above 'Daniel' can access only 'Sales' and 'Marketing', 'Martin' can access 'Sales' and 'Production' and so on. So the required output is:
CREATE TABLE #ExampleOutput
(
[userName] VARCHAR(50),
[Sales] VARCHAR(5),
[Production] VARCHAR(5),
[Marketing] VARCHAR(5)
)
INSERT INTO #ExampleOutput VALUES
('Daniel', 'true', 'false', 'true'),
('Martin', 'true', 'true', 'false'),
('Brandon', 'false', 'true', 'false'),
('Doug', 'false', 'false', 'false')
SELECT * FROM #ExampleOutput
I used pivot query but there are a couple of limitations in it(may be I am making mistakes in it). First, the pivot columns are static whereas I need dynamic column names(Application Modules). Second, I need to include all the users with all the modules with access or without access that I am unable to achieve. Can someone write a sample query to help me how this can be done?
March 24, 2015 at 7:57 am
Does this report have to be written in T-SQL? Using an Excel pivot table with a data connection to the tables would allow for a dynamic output (as long as your users and applications don't exceed the maximum in terms of rows and columns).
March 24, 2015 at 8:07 am
andyscott (3/24/2015)
Does this report have to be written in T-SQL? Using an Excel pivot table with a data connection to the tables would allow for a dynamic output (as long as your users and applications don't exceed the maximum in terms of rows and columns).
As well as Excel you could always use an SSRS report with Matrix to do this simply put Module on Columns and User on Rows.
Either way the result would be the same.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 24, 2015 at 8:11 am
select
u.UserName,
SUM(CASE WHEN ModuleName = 'Sales' THEN 1 ELSE 0 END) AS [Sales],
SUM(CASE WHEN ModuleName = 'Production' THEN 1 ELSE 0 END) AS [Production],
SUM(CASE WHEN ModuleName = 'Marketing' THEN 1 ELSE 0 END) AS [Marketing]
from #ModuleAccess a
INNER JOIN #ApplicationUsers u
on u.userId = a.userId
INNER JOIN #ApplicationModules M
on m.moduleId = a.moduleId
GROUP BY u.UserName
March 24, 2015 at 8:15 am
Opps, missed Doug. Sorry Doug. Select from users and left outer join to other tables required...
SELECT
u.UserName,
SUM(CASE WHEN ModuleName = 'Sales' THEN 1 ELSE 0 END) AS [Sales],
SUM(CASE WHEN ModuleName = 'Production' THEN 1 ELSE 0 END) AS [Production],
SUM(CASE WHEN ModuleName = 'Marketing' THEN 1 ELSE 0 END) AS [Marketing]
FROM #ApplicationUsers u
LEFT OUTER JOIN #ModuleAccess a
on u.userId = a.userId
LEFT OUTER JOIN #ApplicationModules m
on m.moduleId = a.moduleId
GROUP BY u.UserName
Theres a PIVOT function in SQL that will do this and not be so hard coded but I'm old fashioned and not used it enough to remember it.
March 25, 2015 at 12:44 am
Thanks for the reply. Actually the column names are not fixed. They can vary, plus I can not make changes to any table schema. So, here is the working query that I have created:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME([moduleName])
FROM (
SELECT DISTINCT [moduleName]
FROM #ApplicationModules
) AS ApplicationModules
SET @DynamicPivotQuery = '
SELECT * FROM
(
SELECT D.[moduleId], D.[moduleName], U.[UserName] AS [User] FROM
#ApplicationUsers U
LEFT OUTER JOIN #ModuleAccess DA
ON U.[userId] = DA.[userId]
LEFT OUTER JOIN #ApplicationModules D
ON D.[moduleId] = DA.[moduleId]
) A
pivot (MIN(moduleId)for [moduleName] in (' + @ColumnName + ')) as [moduleIdId]
'
EXEC sp_executesql @DynamicPivotQuery
DROP TABLE #ApplicationUsers
DROP TABLE #ModuleAccess
DROP TABLE #ApplicationModules
Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply