MySQL中的數據透視表

我想知道是否可以更改此輸出：

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  

進入這個：

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

我使用的查詢是這樣的：

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;

任何幫助將非常感激。提前致謝！！

這會給你你的結果。

如果您需要相同順序的權限。您需要在 c （聯合表）中添加一列，然後按您想要的順序。

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;

結果

引用自：https://dba.stackexchange.com/questions/297575