Click here to Skip to main content
15,918,976 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
USER TABLE
==========
USER_ID     USER_NAME
1150080     XXXXXXXXXXXX  
1150081     YYYYYYYYYYYY 
1150082     ZZZZZZZZZZZZ 
1150083     RRRRRRRRRRRR
1150084     WWWWWWWWWWWW
1150085     TTTTTTTTTTTT
1150086     EEEEEEEEEEEE
1150087     QQQQQQQQQQQQ



ROLE TABLE
============
ROLE_ID   ROLE_NAME
1         AAAAAAAAA
2         CCCCCCCCC 
3         RRRRRRRRR
4         TTTTTTTTT
5         UUUUUUUUU
6         YYYYYYYYY
7         IIIIIIIII


USERROLE TABLE
===============
USER_ID    ROLE_ID
1150080    1
1150080    2
1150081    1
1150083    1
1150083    7


REQURIED RESAULT

USER_ID      USER_NAME                   ROLE_ID

                                1    2    3   4  5  6   7 
1150080     XXXXXXXXXXXX        ✓    ✓    *   *  *  *   *
1150081     YYYYYYYYYYYY        *    ✓    *   *  *  *   *
1150082     ZZZZZZZZZZZZ        *    *    *   *  *  *   * 
1150083     RRRRRRRRRRRR        ✓    *    *   *  *  *   ✓ 
1150084     WWWWWWWWWWWW        *    *    *   *  *  *   *
1150085     TTTTTTTTTTTT        *    *    *   *  *  *   *
1150086     EEEEEEEEEEEE        *    *    *   *  *  *   *
1150087     QQQQQQQQQQQQ        *    *    *   *  *  *   *


What I have tried:

select SUSER.USER_ID,SUSER.USER_NAME,SUSERROLE.ROLE_ID,SROLEDETAIL.PRODUCT_ID,SPRODUCT.PRODUCT_NAME,SROLEDETAIL.FUNCTION_ID,SFUNCTION.FUNCTION_NAME from SUSER
full join SUSERROLE on SUSER.USER_ID = SUSERROLE.USER_ID
full join SROLEDETAIL on SUSERROLE.ROLE_ID =SROLEDETAIL.ROLE_ID
full join SFUNCTION on SROLEDETAIL.FUNCTION_ID= SFUNCTION.FUNCTION_ID
FULL JOIN SPRODUCT ON SROLEDETAIL.PRODUCT_ID =SPRODUCT.PRODUCT_ID
Posted
Updated 21-Jan-24 22:22pm
v2
Comments
Manjuke Fernando 22-Jan-24 3:57am    
You haven't included what you have tried so far. Are you able to provide the script of your approach ?

Hi,

Anyhow you can use PIVOT and achieve this. If the ROLE_ID's are limited, you can use a static values in your query. But if it's not limited, then you may required to generate your PIVOT query dynamically, and execute it.

Btw, your results is bit off. E.g: user '1150081' doesn't have ROLE_ID '2', instead it should be '1'. I guess it's a typo.

SQL
;WITH SourceQuery AS (
SELECT 
	UT.[USER_ID]
	,[UT].[USER_NAME]
	,[ROLE_ID]
FROM
	USER_TABLE AS [UT]
	LEFT JOIN USERROLE_TABLE AS [UR]
		ON UT.[USER_ID] = [UR].[USER_ID]
)
SELECT
	[USER_ID]
	,[USER_NAME]
	,IIF([1] IS NULL, N'',N'Y') AS [1]
	,IIF([2] IS NULL, N'',N'Y') AS [2]
	,IIF([3] IS NULL, N'',N'Y') AS [3]
	,IIF([4] IS NULL, N'',N'Y') AS [4]
	,IIF([5] IS NULL, N'',N'Y') AS [5]
	,IIF([6] IS NULL, N'',N'Y') AS [6]
	,IIF([7] IS NULL, N'',N'Y') AS [7]
FROM	
	SourceQuery AS Src
PIVOT (
	MAX(ROLE_ID) 
	FOR ROLE_ID
	IN (
		[1],[2],[3],[4],[5],[6],[7]
	)
) AS PVT
ORDER BY
	[USER_ID]


I used "Y" and "" instead of the tick and asterisk since it won't show correctly in the solution pane.

Hope it will help you.
 
Share this answer
 
v2
Comments
Maciej Los 22-Jan-24 11:41am    
5ed!
chamindat 22-Jan-24 21:37pm    
I appreciate it, but the role ID cannot be hard-coded. A new role ID can be added frequently.
Manjuke Fernando 22-Jan-24 22:27pm    
Then I would suggest you to generate the above code dynamically and include the ROLE_ID's from a table.
To generate the matrix with predefined user roles, you can use the below SQL query through SELECT statements and conditional logic. The MAX function is used to consolidate the information for each role within the GROUP BY clause. In this context, 'Y' signifies a checkmark, while 'N' denotes an asterisk."
SQL
SELECT
    u.USER_ID,
    u.USER_NAME,
    MAX(CASE WHEN ur.ROLE_ID = 1 THEN 'Y' ELSE 'N' END) AS [1],
    MAX(CASE WHEN ur.ROLE_ID = 2 THEN 'Y' ELSE 'N' END) AS [2],
    MAX(CASE WHEN ur.ROLE_ID = 3 THEN 'Y' ELSE 'N' END) AS [3],
    MAX(CASE WHEN ur.ROLE_ID = 4 THEN 'Y' ELSE 'N' END) AS [4],
    MAX(CASE WHEN ur.ROLE_ID = 5 THEN 'Y' ELSE 'N' END) AS [5],
    MAX(CASE WHEN ur.ROLE_ID = 6 THEN 'Y' ELSE 'N' END) AS [6],
    MAX(CASE WHEN ur.ROLE_ID = 7 THEN 'Y' ELSE 'N' END) AS [7]
FROM [USER] u
	LEFT JOIN [USERROLE] ur ON u.USER_ID = ur.USER_ID
GROUP BY u.USER_ID, u.USER_NAME
ORDER BY u.USER_ID
 
Share this answer
 
v2
Comments
chamindat 22-Jan-24 21:37pm    
I appreciate it, but the role ID cannot be hard-coded. A new role ID can be added frequently.
M Imran Ansari 23-Jan-24 0:29am    
Sure, proceed with a dynamic query, as also indicated in the comments of solution-1. Kindly check out the provided link for more information on 'Building Dynamic SQL' https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900