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.
;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.