How Can I Efficiently Convert Rows to Columns in MySQL?

mysql row transition conversion detailed explanation

In mysql, the row in the table is converted into a column, and steps are required.

Step 1: Determine the x and y values ​​

First, determine the column of the X -value (columns) and y value (line) of the perspective table. In this example, hostid will be used as a value, and itemname will be used as an X value.

Step 2: Add additional

Add a new column to each X value, which contains the corresponding value. For example, for X -value A, B, and C, add columns A, B, and C using Case statements, as shown below:

Step 3: Grouping and aggregation

<code class="language-sql">SELECT
  history.*,
  CASE WHEN itemname = "A" THEN itemvalue END AS A,
  CASE WHEN itemname = "B" THEN itemvalue END AS B,
  CASE WHEN itemname = "C" THEN itemvalue END AS C
FROM history;</code>

The extension table is paid by the Y value column (in this example), and the new columns are aggregated using the polymerization function (such as SUM).

Step 4: Beautify (optional)

If necessary, you can use a neutral value (such as 0) to replace any NULL value to make the table easier to read:

<code class="language-sql">SELECT
  hostid,
  SUM(A) AS A,
  SUM(B) AS B,
  SUM(C) AS C
FROM history_extended
GROUP BY hostid;</code>

Other precautions

The value used in additional columns can be adjusted according to specific needs.

<code class="language-sql">SELECT
  hostid,
  COALESCE(A, 0) AS A,
  COALESCE(B, 0) AS B,
  COALESCE(C, 0) AS C
FROM history_itemvalue_pivot;</code>

The polymerization function used may vary from the result (such as Count, MAX). By speculating multiple columns in the Group By clause, multiple columns can be used as the Y value.

Although this solution is good for the limited number of perspective tables, it will become very complicated for tables with a large number of columns.

The above is the detailed content of How Can I Efficiently Convert Rows to Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!