How to Pivot Rows into Columns in MySQL?

Detailed explanation of MySQL data table row and column conversion

Introduction

Converting data in a data table in which rows represent observed values ​​and columns represent attributes into a table in which the observed values ​​become columns is a very useful data manipulation technique. This article will walk you through how to implement this conversion in MySQL.

Step 1: Select the target column

Identify the columns that will be the y values ​​(result table rows) and x values ​​(result table columns) in the target pivot table. In this example, "hostid" will be the y value and "itemname" will be the x value.

Step 2: Extend the base table to add additional columns

For each x value, add a new column to the underlying table. Use a CASE expression to populate these columns with the corresponding itemvalue based on itemname.

Step 3: Group and aggregate extended table

Group the extended table by the y-value column and aggregate the x-value column using an appropriate function (such as SUM).

Step 4: Beautify (optional)

Replace any NULL values ​​in the aggregate table with a selected neutral value (e.g. 0) to create visually clearer results.

Example

Consider the following base table:

hostid	itemname	itemvalue
1	A	10
1	B	3
2	A	9
2	C	40

Create a pivot table

<code class="language-sql">-- 步骤 1：选择目标列
SELECT hostid, itemname, itemvalue
FROM history;

-- 步骤 2：扩展基础表添加额外列
CREATE VIEW history_extended AS
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;

-- 步骤 3：分组和聚合扩展表
CREATE VIEW history_itemvalue_pivot AS
SELECT
  hostid,
  SUM(A) AS A,
  SUM(B) AS B,
  SUM(C) AS C
FROM history_extended
GROUP BY hostid;

-- 步骤 4：美化
CREATE VIEW history_itemvalue_pivot_pretty AS
SELECT 
  hostid, 
  COALESCE(A, 0) AS A, 
  COALESCE(B, 0) AS B, 
  COALESCE(C, 0) AS C 
FROM history_itemvalue_pivot;

-- 最终结果
SELECT *
FROM history_itemvalue_pivot_pretty;</code>

Results

hostid	A	B	C
1	10	3	0
2	9	0	40

This example demonstrates how to efficiently reshape a table in MySQL to convert rows into columns.

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