Deconstruct MySQL tables
P粉549986089
2023-08-29 18:24:12
<p>I was wondering if I could change this output to: </p>
<pre class="brush:php;toolbar:false;">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</pre>
<p>It becomes like this:</p>
<pre class="brush:php;toolbar:false;">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</pre>
<p>The query statement I used is: </p>
<pre class="brush:php;toolbar:false;">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;</pre>
<p>Any help would be greatly appreciated. Thank you in advance! </p>
This is an example of "unpivot":
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.