MySQL dynamic query / view for crosstab

Question

I currently have a string encoded representation 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 it is possible to dynamically generate role columns from entries in the role table.

Answer 1

You can do what you want, but I'm not sure why you need it. Once you use dynamic column aliases, how do you plan on referencing them? That is, if you pull out your column aliases from the database, how can you use them? Perhaps I am missing the reason for your question.

In any case, I assume that 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 this you can get 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 |
+----------+---------+-------+

:

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 |
+----------+-------+

, , . MySQL, :

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; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

, , SQL SELECT. :

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 |
+----------+-------+-------+------+------+-------+

-, . GROUP_CONCAT SET @sql, . . , , , , , :

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 |
+----------+-------+-------+------+------+-------+