Unpivoting table in MySQL

Question

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

https://stackoverflow.com/help/minimal-reproducible-example – Aug 04 '21 at 15:27 — , Aug 04 '21 at 15:27

score 0 · Answer 1 · answered Aug 04 '21 at 15:40

Here's an example of an "unpivot":

(SELECT User AS Users, 'Select' AS Privileges, 1 AS PrivOrder, MAX(CASE db WHEN 'performance_schema' THEN Select_priv='Y' END) AS `performance_schema`, MAX(CASE db WHEN 'sys' THEN Select_priv='Y' END) AS `sys` FROM db GROUP BY User)
UNION
(SELECT User, 'Insert', 2, MAX(CASE db WHEN 'performance_schema' THEN Insert_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Insert_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Update', 3, MAX(CASE db WHEN 'performance_schema' THEN Update_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Update_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Delete', 4, MAX(CASE db WHEN 'performance_schema' THEN Delete_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Delete_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Create', 5, MAX(CASE db WHEN 'performance_schema' THEN Create_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Create_priv='Y' END) FROM db GROUP BY User)
...
ORDER BY Users, PrivOrder;

Sorry, there's no way to make a pivot or unpivot query automatically define columns for each schema based on the data it finds. Columns must be set in the query before the query begins reading data.

Unpivoting table in MySQL

1 Answers1