How to create a matrix from a table with three text columns

Question

I have created a view out of some tables which consist data regarding authorisation info for some users. An example:

Role         Page         Right

Admin        Home         RW
Manager      Home         RW
Viewer       Home         R
Admin        Overview     RW
Manager      Overview     R
Viewer       Overview     R
Admin        Settings     RW        

My goal is to create a new view like a matrix, which shows the following:

Page         Admin     Manager   Viewer

Home          RW         RW        R
Overview      RW         R         R
Settings      RW 

Is there a way I can accomplish this in SSMS, keeping in mind that more roles could be added in the future and I don't have to update my code every time a role is created?

Thanks in advance!

Answer 1

You should use PIVOT statement:

SELECT * 
FROM table1 AS t
PIVOT (MAX([RIGHT]) FOR [ROLE] IN ([Admin], [Manager], [Viewer])) AS pvt 

If you have a changing Role column you may use code like this:

DECLARE @ColumnNames NVARCHAR(MAX)  
SELECT @ColumnNames = STUFF(
           (
               SELECT DISTINCT ',['  + [ROLE]+ ']'
               FROM  trtr
               ORDER BY 1 
                      FOR XML PATH('')
           ), 1, 1, '') 

DECLARE @Query AS NVARCHAR(MAX)
SET @Query = 'SELECT * 
FROM trtr AS t
PIVOT (MAX([RIGHT]) FOR [ROLE] IN (' + @ColumnNames + ')) AS pvt'          
EXEC sp_executesql @Query

However you wouldn't be able to use it as a view. You will have to create a procedure