dynamic MySQL query/view for crosstab

Question

I currently have a hard coded view with the following sql:

select username
    ,(case user_role.role_id when 1  then true else false end) as ROLE_SUPER 
    ,(case user_role.role_id when 2  then true else false end) as ROLE_ADMIN
    ,(case user_role.role_id when 3  then true else false end) as ROLE_VIEW
    ,(case user_role.role_id when 4  then true else false end) as ROLE_USER
    ,(case user_role.role_id when 5  then true else false end) as ROLE_EMAIL
    from user 
    left outer join user_role on user.id=user_role.user_id
    left outer join role on user_role.role_id = role.id;

my question is whether or not it is possible to dynamically generate role columns from the records in the role table.

Answer 1

You can do what you want to do, but I am not sure why you would want to. Once you have your dynamic column aliases, how do you plan on referencing them? That is, if you pull your column aliases from the database, how will you then be able to use them? I may be missing the reason behind your question.

Anyway, I assume you have a structure like this:

CREATE TABLE `user` (
    `id` int(11) NOT NULL auto_increment,
    `username` varchar(255) default NULL,
    PRIMARY KEY  (`id`)
);

CREATE TABLE `role` (
    `id` int(11) NOT NULL auto_increment,
    `role` varchar(255) default NULL,
    PRIMARY KEY  (`id`)
);

CREATE TABLE `user_role` (
    `user_id` int(11),
    `role_id` int(11),
    PRIMARY KEY (`user_id`, `role_id`)
);

INSERT INTO `user` (`username`) VALUES
    ('Bob'), ('Alice'), ('Carol'), ('Dave'), ('Eve');

INSERT INTO `role` (`role`) VALUES
    ('Super'), ('Admin'), ('View'), ('User'), ('Email');

INSERT INTO `user_role` VALUES
    (1,1), (2,2), (3,3), (4,4), (5,5);

From that, you can obtain information about users and their role(s):

SELECT username, role.id AS role_id, role.role AS role FROM user_role
JOIN user ON user.id = user_role.user_id
JOIN role ON role.id = user_role.role_id;

+----------+---------+-------+
| username | role_id | role  |
+----------+---------+-------+
| Bob      |       1 | Super |
| Alice    |       2 | Admin |
| Carol    |       3 | View  |
| Dave     |       4 | User  |
| Eve      |       5 | Email |
+----------+---------+-------+

You can also create a column alias for a specific role:

SELECT username, (role.id = 1) AS Super FROM user_role
JOIN user ON user.id = user_role.user_id
JOIN role ON role.id = user_role.role_id;

+----------+-------+
| username | Super |
+----------+-------+
| Bob      |     1 |
| Alice    |     0 |
| Carol    |     0 |
| Dave     |     0 |
| Eve      |     0 |
+----------+-------+

However, if I understand your question correctly, what you want to do is to generate the column alias from the role name. You cannot use a variable as a column alias in a MySQL statement, but you can construct a prepared statement:

SET @sql = (SELECT CONCAT(
    'SELECT username, ',
    GROUP_CONCAT('(role.id = ', id, ') AS ', role SEPARATOR ', '),
    ' FROM user_role ',
    'JOIN user ON user.id = user_role.user_id ',
    'JOIN role ON role.id = user_role.role_id;')
FROM role);

SELECT @sql;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT username, (role.id = 1) AS Super, (role.id = 2) AS Admin, (role.id = 3) AS View, (role.id = 4) AS User, (role.id = 5) AS Email FROM user_role JOIN user ON user.id = user_role.user_id JOIN role ON role.id = user_role.role_id; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

As you will see from the output, that generates a string which contains a SQL SELECT statement. You now need to create a prepared statement from that string, and execute the result:

PREPARE stmt FROM @sql;
EXECUTE stmt;
+----------+-------+-------+------+------+-------+
| username | Super | Admin | View | User | Email |
+----------+-------+-------+------+------+-------+
| Bob      |     1 |     0 |    0 |    0 |     0 |
| Alice    |     0 |     1 |    0 |    0 |     0 |
| Carol    |     0 |     0 |    1 |    0 |     0 |
| Dave     |     0 |     0 |    0 |    1 |     0 |
| Eve      |     0 |     0 |    0 |    0 |     1 |
+----------+-------+-------+------+------+-------+

EDIT

To make calling the crosstab query easier, you could wrap the whole thing up in a stored procedure. In the following example, I could not get the GROUP_CONCAT to work within the SET @sql statement, as it does above. Instead, I had to separate it off into its own variable. I'm not sure why this didn't work, but the end result is the same, and the code is perhaps a little less cryptic:

DELIMITER //
DROP PROCEDURE IF EXISTS test.crosstab//
CREATE PROCEDURE test.crosstab()
BEGIN
    SET @cols = (SELECT GROUP_CONCAT(
        '(role.id = ', id, ') AS ', role
        SEPARATOR ', ') FROM role);
    SET @sql = CONCAT(
        'SELECT username, ',
        @cols,
        ' FROM user_role ',
        'JOIN user ON user.id = user_role.user_id ',
        'JOIN role ON role.id = user_role.role_id;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END;
//
DELIMITER ;

CALL test.crosstab();

+----------+-------+-------+------+------+-------+
| username | Super | Admin | View | User | Email |
+----------+-------+-------+------+------+-------+
| Bob      |     1 |     0 |    0 |    0 |     0 |
| Alice    |     0 |     1 |    0 |    0 |     0 |
| Carol    |     0 |     0 |    1 |    0 |     0 |
| Dave     |     0 |     0 |    0 |    1 |     0 |
| Eve      |     0 |     0 |    0 |    0 |     1 |
+----------+-------+-------+------+------+-------+