Based on the provided table structures, I need following SQL query:

Question

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 21-Jan-24 19:48pm

chamindat

Updated 21-Jan-24 22:22pm

v2

Add a Solution

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 ?

2 solutions

Solution 2

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

Posted 22-Jan-24 8:24am

M Imran Ansari

Updated 22-Jan-24 8:26am

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Manjuke Fernando · Accepted Answer · 2024-01-21T22:20:00

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.