How to Generate a Pivot Table Output Using MySQL Queries?

Return to the perspective table output in mysql

Assuming you have a MySQL table, the organization method is as follows:

Now, the goal is to perform a mysql query to generate output similar to this:

公司名称	操作	页数
公司A	打印	3
公司A	打印	2
公司A	打印	3
公司B	邮件	NULL
公司B	打印	2
公司B	打印	2
公司B	打印	1
公司A	打印	3

In essence, this output represents a perspective table, which classified the data based on the operation of each company's name and the combination of pages.

公司名称	邮件	打印1页	打印2页	打印3页
公司A	0	0	1	3
公司B	1	1	2	0

Solution

To return the perspective table output in MySQL, you can use Case

to define the conditions of each cell and use

Group by to classify the data according to the company name. The following is an example of the SQL statement provided:

This query will the perspective format required for the output.

<code class="language-sql">SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;</code>

Important explanation

For a larger table with more operations/pages, SQL statements will require more case

conditions.

• To adapt to dynamic conditions, you can use pre -processing statements or routine.

The above is the detailed content of How to Generate a Pivot Table Output Using MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!