Group by user and permission-CodePudding

I have a simple table and need to output the data in a way that permissions relevant to the area appear in the same row.

user   |  page    |  permission
-------------------------------
Jon    |  books   |   read
Jon    |  books   |   delete   
Jon    |  photos  |   read
Jon    |  photos  |   edit

Desired output in HTML (not a new table):

user   |   page   |  read  | edit  |  delete
-----------------------------------------------
Jon    |  books   |   X    |       |     X
Jon    |  photos  |   X    |   X   |

Basically I need to output the data and combine all rows that have the same user and page into a single row, where permissions become columns. I can write two queries but it seems to be inefficient to run second query from each row.

I tried grouping but it does not output all the results pertinent to each user:

SELECT *
FROM t1
GROUP BY user, page

What am I missing?

CodePudding user response：

seems you messing some (fake) aggregation function .. try

select user, page
    , max(case when permission = 'read' then 'X' else '' end) read
    , max(case when permission = 'edit' then 'X' else '' end) edit
    , max(case when permission = 'delete' then 'X' else '' end) delete
from t1
group by user, page

CodePudding user response：

SELECT
e.user,
e.page
EXISTS (SELECT user FROM yourTable WHERE e.permission = 'read' AND e.user = user  AND e.page = page) as read,
EXISTS (SELECT user FROM yourTable WHERE e.permission = 'edit' AND e.user = user  AND e.page = page) as edit,
EXISTS (SELECT user FROM yourTable WHERE e.permission = 'delete' AND e.user = user AND e.page = page) as delete
FROM yourTable e
GROUP by e.user, e.page

From: subquery in mysql case when clause