Problem with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Query

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-11 : 08:20:13

Hi ,
I have tables and data as posted below

Table TApplication_menu

App_Menu_NBR Menu_NM
1 Administration
2 Employee
3 Skill Management
4 Leave Management
5 Define Roles
6 Assign roles
7 Leave Application
8 Leave Approval
9 Leave Policy
10 Profile
11 Contact Details
12 Education History

Table – Tpermission

Permission_NBR App_Menu_NBR Permission_NM
-------------- ------------ -----------------------------------------
1 1 READ
2 2 READ
3 3 CREATE
4 4 READ
5 5 READ
6 5 CREATE
7 5 UPDATE
8 5 DELETE
9 6 READ
10 6 CREATE
11 6 UPDATE

Table – Tsystem_Role
Role_NBR Role_NM
1 HR
2 Employee
3 Supervisor
4 Test

Table- Temployee_System_Role

Employee_Role_NBR Employee_NBR Role_NBR
----------------- ------------ ----------- --------- -----------
1 193 1
2 168 1
3 175 3
4 69 1
5 147 1
6 165 1
7 208 1
8 214 1

Based on the Above tables I need a Query. I have tried doing till here. But not sure how to the rest to meer the requirement-

The query which I wrote Is something like this

SELECT DISTINCT
am.Menu_NM
,CASE WHEN p.Permission_NM='READ' THEN 'Y'ELSE NULL END AS [READ]
,CASE WHEN p.Permission_NM='CREATE' THEN 'Y'ELSE NULL END AS [CREATE]
,CASE WHEN p.Permission_NM='UPDATE' THEN 'Y'ELSE NULL END AS [UPDATE]
,CASE WHEN p.Permission_NM='DELETE' THEN 'Y'ELSE NULL END AS [DELETE]
,CASE WHEN p.Permission_NM='SEARCH' THEN 'Y'ELSE NULL END AS [SEARCH]
FROM TEmployee e (NOLOCK)
INNER JOIN TEmployee_System_Role esr (NOLOCK)
ON e.Employee_NBR = esr.Employee_NBR
INNER JOIN TSystem_Role sr (NOLOCK)
ON esr.Role_NBR = sr.Role_NBR
INNER JOIN TSystem_Role_Permission srp (NOLOCK)
ON sr.Role_NBR = srp.Role_NBR
INNER JOIN TPermission p (NOLOCK)
ON srp.Permission_NBR = p.Permission_NBR
INNER JOIN TApplication_Menu am (NOLOCK)
ON p.App_Menu_NBR = am.App_Menu_NBR
WHERE e.Employee_NBR = @User_NBR
AND (am.App_Menu_NBR = @Page_NBR OR @Page_NBR = 0)

When I Execute the above statement in the stored Procedure.. I'm getting the data in this format

Menu_NM READ CREATE UPDATE DELETE SEARCH
-------------------------------------------------- ---- ------ ------ ------ ------
Administration Y NULL NULL NULL NULL
Announcements NULL NULL NULL Y NULL
Announcements NULL NULL Y NULL NULL
Announcements NULL Y NULL NULL NULL
Announcements Y NULL NULL NULL NULL
Assign Projects NULL NULL NULL Y NULL
Assign Projects NULL NULL Y NULL NULL
Assign Projects NULL Y NULL NULL NULL
Assign Projects Y NULL NULL NULL NULL
Assign roles NULL NULL NULL Y NULL
Assign roles NULL NULL Y NULL NULL
Assign roles NULL Y NULL NULL NULL
Assign roles Y NULL NULL NULL NULL
C.Off Application NULL NULL NULL Y NULL
C.Off Application NULL NULL Y NULL NULL
C.Off Application NULL Y NULL NULL NULL
C.Off Application Y NULL NULL NULL NULL

But the Requirement is something like this

Menu_NM READ CREATE UPDATE DELETE SEARCH
Administration Y NULL NULL NULL NULL
Announcements Y Y Y Y NULL
Assign ProjectsY Y Y Y NULL
Assign Roles Y Y Y Y NULL

Is there any way to modify the above query and get the output as above. I'm really sorry.. as i'm unable to format the data which i pasted

Thanks Before Hand

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-12-11 : 09:25:12

I believe all you really need to do is the following

place MAX() around all case statements MAX(CASE .... END) AS
Remove the DISTINCT and at the end do a GROUP BY am.Menu_NM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 09:57:05

this is what prev poster suggested


SELECT	 
am.Menu_NM
,MAX(CASE WHEN p.Permission_NM='READ' THEN 'Y'ELSE NULL END) AS [READ]
,MAX(CASE WHEN p.Permission_NM='CREATE' THEN 'Y'ELSE NULL END) AS [CREATE]
,MAX(CASE WHEN p.Permission_NM='UPDATE' THEN 'Y'ELSE NULL END) AS [UPDATE]
,MAX(CASE WHEN p.Permission_NM='DELETE' THEN 'Y'ELSE NULL END) AS [DELETE]
,MAX(CASE WHEN p.Permission_NM='SEARCH' THEN 'Y'ELSE NULL END) AS [SEARCH]
FROM TEmployee e (NOLOCK)
INNER JOIN TEmployee_System_Role esr (NOLOCK)
ON e.Employee_NBR = esr.Employee_NBR
INNER JOIN TSystem_Role sr (NOLOCK)
ON esr.Role_NBR = sr.Role_NBR
INNER JOIN TSystem_Role_Permission srp (NOLOCK)
ON sr.Role_NBR = srp.Role_NBR
INNER JOIN TPermission p (NOLOCK)
ON srp.Permission_NBR = p.Permission_NBR
INNER JOIN TApplication_Menu am (NOLOCK)
ON p.App_Menu_NBR = am.App_Menu_NBR
WHERE e.Employee_NBR = @User_NBR
AND (am.App_Menu_NBR = @Page_NBR OR @Page_NBR = 0)
GROUP BY am.Menu_NM

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-12 : 00:30:56

Thanks a Lot for the solution.. It works perfectly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 02:08:27

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources