Pivoting table in MySQL

All we need is an easy explanation of the problem, so here it is.

I was wondering if it is possible to change the output of this:

User           Database            Select  Insert  Update  Delete  Create  References  Alter   Drop    
-------------  ------------------  ------  ------  ------  ------  ------  ----------  ------  --------
mysql.session  performance_schema  1       0       0       0       0       0           0       0       
mysql.sys      sys                 0       0       0       0       0       0           0       0  

into this:

Users               Privileges      performance_schema      sys
-----               ----------      ------------------      ---
mysql.session       Select                  1       
mysql.session       Insert                  0       
mysql.session       Update                  0       
mysql.session       Delete                  0       
mysql.session       Create                  0       
mysql.session       References              0       
mysql.session       Alter                   0       
mysql.session       Drop                    0       
mysql.sys           Select                                  0
mysql.sys           Insert                                  0
mysql.sys           Update                                  0
mysql.sys           Delete                                  0
mysql.sys           Create                                  0
mysql.sys           References                              0
mysql.sys           Alter                                   0
mysql.sys           Drop                                    0

The query I used is this:

SELECT 
  DISTINCT
  USER "User",
  db "Database",
  IF(Select_priv = 'Y', '1 ', '0') AS "Select",
  IF(Insert_priv = 'Y', '1 ', '0') AS "Insert",
  IF(Update_priv = 'Y', '1', '0') AS "Update",
  IF(Delete_priv = 'Y', '1', '0') AS "Delete",
  IF(Create_priv = 'Y', '1', '0') AS "Create",
  IF(References_priv = 'Y', '1', '0') AS "References",
  IF(Alter_priv = 'Y', '1', '0') AS "Alter",
  IF(Drop_priv = 'Y', '1', '0') AS "Drop"
 FROM
    mysql.db
 ORDER BY
    USER, Db;

Any help would be really appreciated. Thanks in advance!!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This will get you your result.

If you need the privileges in the same order . you need to add a column to the c (the union table) with then order you want.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(s.Database = "', `Database`,'", `data`,"")) AS "',`Database`,'"')
              ) INTO @sql
FROM (select t.user AS Users,
     t.db  "Database",
       c.Privileges,
       case c.col
         when 'Select_priv' then IF(Select_priv = 'Y', '1 ', '0') 
         when 'Insert_priv' then IF(Insert_priv = 'Y', '1 ', '0')
         when 'Update_priv' then IF(Update_priv = 'Y', '1', '0') 
         when 'Delete_priv' then IF(Delete_priv = 'Y', '1', '0') 
         when 'Create_priv' then IF(Create_priv = 'Y', '1 ', '0')
         when 'References_priv' then IF(References_priv = 'Y', '1', '0') 
        when 'Alter_priv' then IF(Alter_priv = 'Y', '1 ', '0')
         when 'Drop_priv' then IF(Drop_priv = 'Y', '1', '0') 
       end as data
     from mysql.db t
     cross join
     (
       select 'Select_priv' as col, 'Select' AS Privileges      
       union all select 'Insert_priv',"Insert"
       union all select 'Update_priv',"Update"
       union all select 'Delete_priv',"Delete"
       union all select 'Create_priv',"Create"
       union all select 'References_priv',"References"
       union all select 'Alter_priv',"Alter"
       union all select 'Drop_priv',"Drop"
     ) c
 ORDER BY
    USER ) table1;


SET @sql = CONCAT('SELECT s.Users,s.Privileges,  ', @sql, ' 
                  FROM (select t.user AS Users,
     t.db  "Database",
       c.Privileges,
       case c.col
         when "Select_priv" then IF(Select_priv = "Y", "1 ", "0") 
         when "Insert_priv" then IF(Insert_priv = "Y", "1", "0")
         when "Update_priv" then IF(Update_priv = "Y", "1", "0") 
         when "Delete_priv" then IF(Delete_priv = "Y", "1", "0") 
         when "Create_priv" then IF(Create_priv = "Y", "1", "0")
         when "References_priv" then IF(References_priv = "Y", "1", "0") 
        when "Alter_priv" then IF(Alter_priv = "Y", "1", "0")
         when "Drop_priv" then IF(Drop_priv = "Y", "1", "0") 
       end as data
     from mysql.db t
     cross join
     (
       select "Select_priv" as col, "Select" AS Privileges      
       union all select "Insert_priv","Insert"
       union all select "Update_priv","Update"
       union all select "Delete_priv","Delete"
       union all select "Create_priv","Create"
       union all select "References_priv","References"
       union all select "Alter_priv","Alter"
       union all select "Drop_priv","Drop"
     ) c
 ORDER BY
    USER )  s
                 GROUP BY s.Users,s.Privileges
                 ORDER BY s.Users');
#SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

Result
Pivoting table in MySQL

Method 2

Step 1 — unpivot to get 4 columns — user, db, priv, value

Step 2 — pivot to move db into columns.

(Your tentative code is more of a "pivot" on the privs. So it is not useful for either step.)

I suggest you think about how the end result will look and how useful (or not useful it will be). And how bulky it will be.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply