Example code of how to implement pivot table in MySQL/MariaDB

The previous article introduced several methods for implementing pivot tables in Oracle. Today, let’s see how to implement the same function in MySQL/MariaDB.

The sample data used in this article can be downloaded here.

Using CASE expressions and grouping aggregation

The essence of a pivot table is to group data according to different combinations of rows and columns, and then summarize the results; therefore, it is very similar to the functionality of grouping (GROUP BY) plus aggregate functions (COUNT, SUM, AVG, etc.) in a database.

We first aggregate the sales data using the following GROUP BY clause:

select coalesce(product, '【All Products】') "Product",
    coalesce(channel, '【All channels】') "Channel",
    any_value(coalesce(extract(year_month from saledate), '【All months】')) "Month",
    sum(amount) "sales volume"
from sales_data
group by product,channel,extract(year_month from saledate) with rollup;

The above statements summarize by product, channel, and month; the with rollup option is used to generate subtotals, totals, and grand totals at different levels; the coalesce function is used to display NULL values in the summary row as corresponding information; the any_value function is used to return any data within the group. If it is removed, a syntax error will be returned (a bug in MySQL). The query returns the following results:

Product |Channel |Month |Sales Volume |
---------|---------|-----------|-------|
Orange |JD |201901 | 41289|
Orange |JD |201902 |43913|
Orange |JD |201903 | 49803|
Orange |JD.com |201904 | 49256|
Orange |JD.com |201905 | 64889|
Orange |JD |201906 |62649|
Orange |JD.com |【All Months】| 311799|
Orange |Store |201901 | 41306|
Orange |Store |201902 | 37906|
Orange |Store |201903 | 48866|
Orange |Store |201904 | 48673|
Orange |Store |201905 | 58998|
Orange |Store |201906 | 58931|
Orange |Store |【All Months】| 294680|
Orange |Taobao |201901 | 43488|
Orange |Taobao |201902 | 37598|
Orange |Taobao |201903 | 48621|
Orange |Taobao |201904 | 49919|
Orange | Taobao | 201905 | 58530|
Orange |Taobao |201906 | 64626|
Orange |Taobao |【All Months】| 302782|
Orange |【All channels】|【All months】| 909261|
...
Banana |【All channels】|【All months】| 925369|
【All products】|【All channels】|【All months】|2771682|

In fact, we have already obtained the summary results of sales, but we need to display the data in different columns according to different months; that is, we need to convert rows into columns. This function can be achieved using the CASE expression:

select coalesce(product, '【All Products】') "Product", coalesce(channel, '【All Channels】') "Channel", 
    sum(case extract(year_month from saledate) when 201901 then amount else 0 end) "January",
    sum(case extract(year_month from saledate) when 201902 then amount else 0 end) "February",
    sum(case extract(year_month from saledate) when 201903 then amount else 0 end) "三月",
    sum(case extract(year_month from saledate) when 201904 then amount else 0 end) "April",
    sum(case extract(year_month from saledate) when 201905 then amount else 0 end) "May",
    sum(case extract(year_month from saledate) when 201906 then amount else 0 end) "六月",
    sum(amount) "total"
from sales_data
group by product, channel with rollup;

The CASE expression in the first SUM function only summarizes the sales in January 2019, and sets the sales in other months to 0. The subsequent SUM functions are similar, and the sales summary for each month and the total for all months are obtained. The PivotTable returned by this query is as follows:

Product |Channel |January |February |March |April |May |June |Total |
----------|----------|------|------|------|------|------|------|------|-------|
Orange |JD.com | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
Orange |Store | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
Orange |Taobao | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
Orange |【All channels】|126083|119417|147290|147848|182417|186206| 909261|
Apple |JD.com | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
Apple |Stores | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
Apple |Taobao | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
Apple |【All channels】|125083|124421|150230|170360|180136|186822| 937052|
Banana |JD.com | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
Banana |Store | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
Banana |Taobao | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
Banana |【All channels】|120557|118356|155412|161857|181689|187498| 925369|
【All products】|【All channels】|371723|362194|452932|480065|544242|560526|2771682|

The IF(expr1, expr2, expr3) function in MySQL can also be used to replace the above CASE expression.

There are cases where rows are converted to columns and columns are converted to rows. MySQL does not have a special function to handle this situation. You can use the UNION operator to merge multiple result sets. For example:

with d as (
 select product, channel,
     sum(case extract(year_month from saledate) when 201901 then amount else 0 end) s01,
     sum(case extract(year_month from saledate) when 201902 then amount else 0 end) s02,
     sum(case extract(year_month from saledate) when 201903 then amount else 0 end) s03,
     sum(case extract(year_month from saledate) when 201904 then amount else 0 end) s04,
     sum(case extract(year_month from saledate) when 201905 then amount else 0 end) s05,
     sum(case extract(year_month from saledate) when 201906 then amount else 0 end) s06
 from sales_data
 group by product, channel
)
select product, channel, 201901 saledate, s01 amount from d
union all
select product, channel, 201902 saledate, s02 from d
union all
select product, channel, 201903 saledate, s03 from d
union all
select product, channel, 201904 saledate, s04 from d
union all
select product, channel, 201905 saledate, s05 from d
union all
select product, channel, 201906 saledate, s06 from d;

The common table expression (with clause) constructs sales data for multiple months, with each month as a column; each query then returns data for one month, and all results are merged together using the union all operator.

Using precompiled dynamic SQL statements

The method of using CASE expressions and aggregate functions to implement pivot tables has certain limitations. If we still need to count the sales from July to December, we need to modify the query statement to add this part of the processing. To this end, we can use dynamic SQL to automatically generate statements for row and column conversion:

select group_concat(
 distinct concat(
  ' sum(case extract(year_month from saledate) when ', dt,
  ' then amount else 0 end) as "', dt, '"')
 ) into @sql
from (
 select extract(year_month from saledate) as dt
 from sales_data
 order by saledate
) d;

set @sql
 = concat('select coalesce(product, ''【All Products】'') "Product", coalesce(channel, ''【All Channels】'') "Channel",', @sql,
      ', sum(amount) "total"
      from sales_data
      group by product, channel with rollup;');
select @sql;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

First, we query the sales_data table to find out all the months and construct a sum function, and store the constructed statement in the variable @sql; the group_concat function can combine multiple lines of strings into a single string.

The maximum length (in bytes) allowed to be returned by the group_concat function is set by the system variable group_concat_max_len, and the default value is 1024.

Then, use the set command to merge the rest of the query statement with the existing content. The generated query statement is as follows:

select coalesce(product, '【All Products】') "Product", coalesce(channel, '【All Channels】') "Channel", 
    sum(case extract(year_month from saledate) when 201901 then amount else 0 end) as "201901", 
    sum(case extract(year_month from saledate) when 201902 then amount else 0 end) as "201902", 
    sum(case extract(year_month from saledate) when 201903 then amount else 0 end) as "201903", 
    sum(case extract(year_month from saledate) when 201904 then amount else 0 end) as "201904", 
    sum(case extract(year_month from saledate) when 201905 then amount else 0 end) as "201905", 
    sum(case extract(year_month from saledate) when 201906 then amount else 0 end) as "201906", 
    sum(amount) "total"
from sales_data
group by product, channel with rollup;

Finally, the statement is executed through the precompiled command and the results are returned. Even if the sales data of other months are added, there is no need to manually modify the query statement.

Using the CONNECT storage engine

If you use MariaDB 10.0 or later, you can use the PIVOT table type in the CONNECT storage engine to implement pivot tables.

First, we need to install the CONNECT storage engine. On Windows systems, you can execute the following command to perform dynamic installation:

INSTALL SONAME 'ha_connect';

You can also add the following content to the configuration file my.ini, but you need to restart the service:

[mysqld]
plugin_load_add = ha_connect

For Linux systems, the installation process can refer to the official documentation.

Next we define a pivot table:

create table pivot_sales(
 product varchar(20) not null,
 channel varchar(20) not null,
 `201901` decimal(10,2) not null flag=1,
 `201902` decimal(10,2) not null flag=1,
 `201903` decimal(10,2) not null flag=1,
 `201904` decimal(10,2) not null flag=1,
 `201905` decimal(10,2) not null flag=1,
 `201906` decimal(10,2) not null flag=1
)
engine=connect table_type=pivot
option_list='PivotCol=saledate,FncCol=amount,host=127.0.0.1,user=root, password=p123456,port=3306'
SrcDef='select product,channel,date_format(saledate, ''%Y%m'') saledate,sum(amount) amount from sales_data group by product,channel,date_format(saledate, ''%Y%m'')';

Among them, engine defines the storage engine as connect; table_type defines the table type as pivot; option_list is used to define various options, PivotCol indicates the column where the data to be converted into multiple fields is located, FncCol specifies the field to be summarized, and the others are information for connecting to the source table server; SrcDef is used to specify the source table query statement, and Tabname can also be used to specify the table name; the fields above are the structure of the pivot table, and flag=1 indicates the fields after aggregation.

After successful creation, we can directly query the data in the pivot_sales table:

select * from pivot_sales;

product |channel |201901 |201902 |201903 |201904 |201905 |201906 |
--------|---------|--------|--------|--------|--------|--------|
Orange|JD|41289.00|43913.00|49803.00|49256.00|64889.00|62649.00|
Orange|Store|41306.00|37906.00|48866.00|48673.00|58998.00|58931.00|
Orange|Taobao|43488.00|37598.00|48621.00|49919.00|58530.00|64626.00|
Apple|JD.com|38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|
Apple|Store|43845.00|40539.00|44909.00|55646.00|56771.00|64933.00|
Apple|Taobao|42969.00|43289.00|48769.00|58052.00|58872.00|59844.00|
Banana|JD|36879.00|36981.00|51748.00|54801.00|64936.00|60688.00|
Banana|Store|41210.00|39420.00|50884.00|52085.00|60249.00|67597.00|
Banana|Taobao|42468.00|41955.00|52780.00|54971.00|56504.00|59213.00|

Currently, the PIVOT table supports limited functions and can only perform some basic operations. For example:

-- No error select * from pivot_sales
where channel = 'JD.com';

-- Syntax error select channel from pivot_sales
where channel = 'JD.com';

This is the end of this article about how to implement pivot table sample code in MySQL/MariaDB. For more relevant MySQL/MariaDB pivot table content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: