Deconstruct MySQL tables

Question

I was wondering if I could change this output to:

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

It becomes like 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 statement I used is:

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 greatly appreciated. Thank you in advance!

Answer 1

This is an example of "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 is no way to automatically define pivot or unpivot queries for columns per schema based on the data found by the query. Columns must be set in the query before the query starts reading data.