Create an output matrix from a table of pairs

Question

I have a table of roles versus permissions as such:

role | permission    
r1   | p1
r2   | p1
r2   | p2
r3   | p1
r3   | p2
r3   | p3

What I want is an output table where the columns are (role and then each permissions). Where the permission/role pair exists in the table I want a 1, where it doesn't exist I want a 0.

Here is the output:

role | p1 | p2 | p3
r1   | 1  | 0  | 0
r2   | 1  | 1  | 0
r3   | 1  | 1  | 1

That is easy, but here is the kicker...neither roles or permissions are fixed length, new ones can be added at any time so I need this to be taken into account.

I don't think this is possible due to the nature of SQL but I thought I'd ask.

Please add the expected output for the given data. Which database? — TechDo, Nov 20 '12 at 11:49
This is called "pivoting the table" and how it's done depends - as techdo already asked - on the used database system. — AndreKR, Nov 20 '12 at 11:54
This is a valid question imo, but is a possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Leigh, Nov 20 '12 at 17:25

score 1 · Accepted Answer · answered Nov 20 '12 at 12:22

You did not specify what RDBMS you are using - so here are solutions for both MySQL and SQL Server.

MySQL:

This is a PIVOT but MySQL does not have a PIVOT function so you will need to replicate it using an aggregate function and a CASE statement.

Static Version - is where you know all of the values beforehand:

select role,
  sum(case when permission = 'p1' then 1 else 0 end) p1,
  sum(case when permission = 'p2' then 1 else 0 end) p2,
  sum(case when permission = 'p3' then 1 else 0 end) p3
from yourtable
group by role;

See SQL Fiddle with Demo

If you don't know the values to turn to column beforehand then you can use a prepared statement:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when permission = ''',
      permission,
      ''' then 1 else 0 end) AS ',
      permission
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT role, ', @sql, ' 
                  FROM yourtable 
                   GROUP BY role');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

SQL Server:

There is a PIVOT function in sql server and you can either hard-code the values or use dynamic sql.

Static Version:

select *
from 
(
  select role, permission
  from yourtable
) src
pivot
(
  count(permission)
  for permission in ([p1], [p2], [p3])
) piv;

See SQL Fiddle with Demo

Dynamic Version:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(permission) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT role, ' + @cols + ' from 
             (
                select role, permission
                from yourtable
            ) x
            pivot 
            (
                count(permission)
                for permission in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

The result from all versions is:

| ROLE | P1 | P2 | P3 |
-----------------------
|   r1 |  1 |  0 |  0 |
|   r2 |  1 |  1 |  0 |
|   r3 |  1 |  1 |  1 |

Create an output matrix from a table of pairs

1 Answers1