I'm trying to generate a security matrix as a report for my application.
Looking at this question, it is almost exactly what I want.
SQL Server: Examples of PIVOTing String data
I've changed the solution
SELECT Action,
MAX( CASE data WHEN 'View' THEN data ELSE '' END ) ViewCol,
MAX( CASE data WHEN 'Edit' THEN data ELSE '' END ) EditCol
FROM t
GROUP BY Action
to adapt to my solution.
SELECT RefRoleId ,
MAX( CASE Permission WHEN 'AdministerErrors' THEN 'X' ELSE '' END ) 'Administer Errors'
FROM RolePermission
GROUP BY RefRoleId
The problem I can see though is that I would need to know exactly which permissions I have and hard code them as columns into the query. I would need a new
MAX( CASE Permission WHEN 'AdministerErrors' THEN 'X' ELSE '' END ) 'Administer Errors'
for every single permission.
Is there a way for me to add the columns I want displayed to an SQL query via a query?
