未加星标

Pivot Tables in MySQL

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
Briefly on Pivot tables

This article deals with the transformation of table data from rows to columns. Such transformation is called pivoting tables. Often, the result of the pivot is a summary table in which statistical data are presented in the form suitable or required for a report.

Besides, such data transformation can be useful if a database is not normalized and the information is stored therein in a non-optimal form. So, when reorganizing the database and transferring data to new tables or generating a required data representation, data pivot can be helpful, i.e. moving values from rows to resulting columns.

Below is an example of the old table of products ProductsOld and the new one ― ProductsNew. It is through the transformation from rows to columns that such a result can be easily achieved.


Pivot Tables in MySQL
Pivoting data by means of tools (dbForge Studio for mysql)

There are applications that have tools allowing to implement data pivot in a convenient graphical environment. For example, dbForge Studio for MySQL includes such functionality that provides the desired result in just a few steps.

Let’s look at the example with a simplified table of orders PurchaseOrderHeader .

CREATE TABLE PurchaseOrderHeader (
PurchaseOrderID INT(11) NOT NULL,
EmployeeID INT(11) NOT NULL,
VendorID INT(11) NOT NULL,
PRIMARY KEY (PurchaseOrderID)
);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (1, 258, 1580);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (2, 254, 1496);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (3, 257, 1494);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (4, 261, 1650);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (5, 251, 1654);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (6, 253, 1664);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (7, 255, 1678);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (8, 256, 1616);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (9, 259, 1492);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (10, 250, 1602);
INSERT PurchaseOrderHeader(PurchaseOrderID, EmployeeID, VendorID) VALUES (11, 258, 1540);
...

Click the link to see the full script of creating the table ― PurchaseOrderHeader.sql.

Assume that we need to make a selection from the table and determine the number of orders made by certain customers from specific suppliers. The list of customers, for which information is needed 250, 251, 252, 253, 254.

A preferred view for the report is as follows.


Pivot Tables in MySQL

The left column VendorID shows the IDs of vendors; columns Emp250 , Emp251 , Emp252 , Emp253 , and Emp254 display the number of orders.

To achieve this in dbForge Studio for MySQL, you need to:

Add the table as a data source for the ‘Pivot Table’ representation of the document. In Database Explorer, right-click the PurchaseOrderHeader table and select Send to and then Pivot Table in the popup menu. Specify a column the values of which will be rows. Drag the VendorID column to the ‘Drop Rows Fields Here’ box. Specify a column the values of which will be columns. Drag the EmployeeID column to the ‘Drop Column Fields Here’ box. You can also set a filter for the required customers (250, 251, 252, 253, 254). Specify a column, the values of which will be the data. Drag the PurchaseOrderID column to the ‘Drop Data Items Here’ box. In the properties of the PurchaseOrderID column, specify the type of aggregation Count of values .

We quickly got the result we need.


Pivot Tables in MySQL
Pivoting data by means of SQL

Of course, data transformation can be performed by means of a database by writing a SQL query. But there is a slight hitch, MySQL does not have a specific statement allowing to do this.

T-SQL-based example for SQL Server

For example, SqlServer and Oracle have the PIVOT operator that allows making such data transformation. If we worked with SqlServer, our query would look like this.

SELECT
VendorID
,[250] AS Emp1
,[251] AS Emp2
,[252] AS Emp3
,[253] AS Emp4
,[254] AS Emp5
FROM (SELECT
PurchaseOrderID
,EmployeeID
,VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT(PurchaseOrderID) FOR EmployeeID IN ([250], [251], [252], [253], [254])
) AS t
ORDER BY t.VendorID; Example for MySQL

In MySQL, we will have to use the means of SQL. The data should be grouped by the vendor column VendorID , and for each required customer ( EmployeeID ), you need to create a separate column with an aggregate function.

In our case, we need to calculate the number of orders, so we will use the aggregate function COUNT.

In the source table, the information on all customers is stored in one column EmployeeID , and we need to calculate the number of orders for a particular customer, so we need to teach our aggregate function to process only certain rows.

The aggregate function does not take into account NULL values, and we use this peculiarity for our purposes.

You can use the conditional operator IF or CASE which will return a specific value for the desired customer, otherwise will simply return NULL; as a result, the COUNT function will count only non-NULL values.

The resulting query is as follows:

SELECT
VendorID,
COUNT(IF(EmployeeID = 250, PurchaseOrderID, NULL)) AS Emp250,
COUNT(IF(EmployeeID = 251, PurchaseOrderID, NULL)) AS Emp251,
COUNT(IF(EmployeeID = 252, PurchaseOrderID, NULL)) AS Emp252,
COUNT(IF(EmployeeID = 253, PurchaseOrderID, NULL)) AS Emp253,
COUNT(IF(EmployeeID = 254, PurchaseOrderID, NULL)) AS Emp254
FROM
PurchaseOrderHeader p
WHERE
p.EmployeeID BETWEEN 250 AND 254
GROUP BY
VendorID;

Or even like this:

VendorID,
COUNT(IF(EmployeeID = 250, 1, NULL)) AS Emp250,
COUNT(IF(EmployeeID = 251, 1, NULL)) AS Emp251,
COUNT(IF(EmployeeID = 252, 1, NULL)) AS Emp252,
COUNT(IF(EmployeeID = 253, 1, NULL)) AS Emp253,
COUNT(IF(EmployeeID = 254, 1, NULL)) AS Emp254
FROM
PurchaseOrderHeader p
WHERE
p.EmployeeID BETWEEN 250 AND 254
GROUP BY
VendorID;

When executed, a familiar result is obtained.


Pivot Tables in MySQL
Automating data pivot, creating query dynamically

As can be seen, the query has a certain consistency, i.e. all the transformed columns are formed in a similar manner, and in order to write the query, you need to know the specific values from the table. To form a pivot query, you need to review all the possible values and only then you should write the query. Alternatively, you can pass this task to a server causing it to obtain these values and dynamically perform the routine task.

Let’s return to the first example, in which we formed the new table ProductsNew from the ProductsOld table. There, the values of properties are limited, and we can’t even know all the possible values; we only have the information on where the names of the properties are stored and their value. These are the Property and Value columns, respectively.

The whole algorithm of creating the SQL query comes down to obtaining the values, from which new columns and concatenations of unchangeable parts of the query will be formed.

SELECT
GROUP_CONCAT(
CONCAT(
' MAX(IF(Property = ''',
t.Property,
''', Value, NULL)) AS ',
t.Property
)
) INTO @PivotQuery
FROM
(SELECT
Property
FROM
ProductOld
GROUP BY
Property) t;
SET @PivotQuery = CONCAT('SELECT ProductID,', @PivotQuery, ' FROM ProductOld GROUP BY ProductID');

Variable @PivotQuery will store our query, the text has been formatted for clarity.

SELECT
ProductID,
MAX(IF(Property = 'Color', Value, NULL)) AS Color,
MAX(IF(Property = 'Name', Value, NULL)) AS Name,
MAX(IF(Property = 'ProductNumber', Value, NULL)) AS ProductNumber,
MAX(IF(Property = 'Size', Value, NULL)) AS Size,
MAX(IF(Property = 'SizeUnitMeasureCode', Value, NULL)) AS SizeUnitMeasureCode
FROM
ProductOld
GROUP BY
ProductID

After executing it, we will obtain the desired result corresponding to the scheme of the ProductsNew table.

What is more, the query from variable @PivotQuery can be executed in the script using MySQL statement EXECUTE.

PREPARE statement FROM @PivotQuery;
EXECUTE statement;
DEALLOCATE PREPARE statement;

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLHeadCUUTSQL ServerRYRIM4GEPA
分页:12
转载请注明
本文标题:Pivot Tables in MySQL
本站链接:http://www.codesec.net/view/521654.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(28)