Pivoting in MariaDB
The problem
You want to "pivot" the data so that a linear list of values with two keys becomes a spreadsheet-like array. See examples, below.
A solution
The best solution is probably to do it in some form of client code (PHP, etc). MySQL and MariaDB do not have a syntax for SELECT that will do the work for you. The code provided here uses a stored procedure to generate code to pivot the data, and then runs the code.
You can edit the SQL generated by the stored procedure to tweak the output in a variety of ways. Or you can tweak the stored procedure to generate what you would prefer.
Reference code for solution
'Source' this into the mysql commandline tool:
DELIMITER //
DROP PROCEDURE IF EXISTS Pivot //
CREATE PROCEDURE Pivot(
IN tbl_name VARCHAR(99), -- table name (or db.tbl)
IN base_cols VARCHAR(99), -- column(s) on the left, separated by commas
IN pivot_col VARCHAR(64), -- name of column to put across the top
IN tally_col VARCHAR(64), -- name of column to SUM up
IN where_clause VARCHAR(99), -- empty string or "WHERE ..."
IN order_by VARCHAR(99) -- empty string or "ORDER BY ..."; usually the base_cols
)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
-- Find the distinct values
-- Build the SUM()s
SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
-- select @subq;
SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
SET @cc3 = REPLACE(@cc2, '&t', tally_col);
-- select @cc2, @cc3;
SET @qval = CONCAT("'\"', val, '\"'");
-- select @qval;
SET @cc4 = REPLACE(@cc3, '&v', @qval);
-- select @cc4;
SET SESSION group_concat_max_len = 10000; -- just in case
SET @stmt = CONCAT(
'SELECT GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n") INTO @sums',
' FROM ( ', @subq, ' ) AS top');
select @stmt;
PREPARE _sql FROM @stmt;
EXECUTE _sql; -- Intermediate step: build SQL for columns
DEALLOCATE PREPARE _sql;
-- Construct the query and perform it
SET @stmt2 = CONCAT(
'SELECT ',
base_cols, ',\n',
@sums,
',\n SUM(', tally_col, ') AS Total'
'\n FROM ', tbl_name, ' ',
where_clause,
' GROUP BY ', base_cols,
'\n WITH ROLLUP',
'\n', order_by
);
select @stmt2; -- The statement that generates the result
PREPARE _sql FROM @stmt2;
EXECUTE _sql; -- The resulting pivot table ouput
DEALLOCATE PREPARE _sql;
-- For debugging / tweaking, SELECT the various @variables after CALLing.
END;
//
DELIMITER ;
Then do a CALL, like in the examples, below.
Variants
I thought about having several extra options for variations, but decided that would be too messy. Instead, here are instructions for implementing the variations, either by capturing the SELECT that was output by the Stored Procedure, or by modifying the SP, itself.
- The data is strings (not numeric) -- Remove "SUM" (but keep the expression); remove the SUM...AS TOTAL line.
- If you want blank output instead of 0 -- Currently the code says "SUM(IF(... 0))"; change the 0 to NULL, then wrap the SUM: IFNULL(SUM(...), ''). Note that this will distinguish between a zero total (showing '0') and no data (blank).
- Fancier output -- Use PHP/VB/Java/etc.
- No Totals at the bottom -- Remove the WITH ROLLUP line from the SELECT.
- No Total for each row -- Remove the SUM...AS Total line from the SELECT.
- Change the order of the columns -- Modify the ORDER BY 1 ('1' meaning first column) in the SELECT DISTINCT in the SP.
- Example: ORDER BY FIND_IN_SET(DAYOFWEEK(...), 'Sun,Mon,Tue,Wed,Thu,Fri,Sat')
Notes about "base_cols":
- Multiple columns on the left, such as an ID and its meaning -- This is already handled by allowing base_cols to be a commalist like 'id, meaning'
- You cannot call the SP with "foo AS 'blah'" in hopes of changing the labels, but you could edit the SELECT to achieve that goal.
Notes about the "Totals":
- If "base_cols" is more than one column, WITH ROLLUP will be subtotals as well as a grand total.
- NULL shows up in the Totals row in the "base_cols" column; this can be changed via something like IFNULL(..., 'Totals').
Example 1 - Population vs Latitude in US
-- Sample input:
+-------+----------------------+---------+------------+
| state | city | lat | population |
+-------+----------------------+---------+------------+
| AK | Anchorage | 61.2181 | 276263 |
| AK | Juneau | 58.3019 | 31796 |
| WA | Monroe | 47.8556 | 15554 |
| WA | Spanaway | 47.1042 | 25045 |
| PR | Arecibo | 18.4744 | 49189 |
| MT | Kalispell | 48.1958 | 18018 |
| AL | Anniston | 33.6597 | 23423 |
| AL | Scottsboro | 34.6722 | 14737 |
| HI | Kaneohe | 21.4181 | 35424 |
| PR | Candelaria | 18.4061 | 17632 |
...
-- Call the Stored Procedure:
CALL Pivot('World.US', 'state', '5*FLOOR(lat/5)', 'population', '', '');
-- SQL generated by the SP:
SELECT state,
SUM(IF(5*FLOOR(lat/5) = "15", population, 0)) AS "15",
SUM(IF(5*FLOOR(lat/5) = "20", population, 0)) AS "20",
SUM(IF(5*FLOOR(lat/5) = "25", population, 0)) AS "25",
SUM(IF(5*FLOOR(lat/5) = "30", population, 0)) AS "30",
SUM(IF(5*FLOOR(lat/5) = "35", population, 0)) AS "35",
SUM(IF(5*FLOOR(lat/5) = "40", population, 0)) AS "40",
SUM(IF(5*FLOOR(lat/5) = "45", population, 0)) AS "45",
SUM(IF(5*FLOOR(lat/5) = "55", population, 0)) AS "55",
SUM(IF(5*FLOOR(lat/5) = "60", population, 0)) AS "60",
SUM(IF(5*FLOOR(lat/5) = "70", population, 0)) AS "70",
SUM(population) AS Total
FROM World.US GROUP BY state
WITH ROLLUP
-- Output from that SQL (also comes out of the SP):
+-------+---------+--------+----------+----------+----------+----------+---------+-------+--------+------+-----------+
| state | 15 | 20 | 25 | 30 | 35 | 40 | 45 | 55 | 60 | 70 | Total |
+-------+---------+--------+----------+----------+----------+----------+---------+-------+--------+------+-----------+
| AK | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60607 | 360765 | 4336 | 425708 |
| AL | 0 | 0 | 0 | 1995225 | 0 | 0 | 0 | 0 | 0 | 0 | 1995225 |
| AR | 0 | 0 | 0 | 595537 | 617361 | 0 | 0 | 0 | 0 | 0 | 1212898 |
| AZ | 0 | 0 | 0 | 4708346 | 129989 | 0 | 0 | 0 | 0 | 0 | 4838335 |
...
| FL | 0 | 34706 | 9096223 | 1440916 | 0 | 0 | 0 | 0 | 0 | 0 | 10571845 |
| GA | 0 | 0 | 0 | 2823939 | 0 | 0 | 0 | 0 | 0 | 0 | 2823939 |
| HI | 43050 | 752983 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 796033 |
...
| WY | 0 | 0 | 0 | 0 | 0 | 277480 | 0 | 0 | 0 | 0 | 277480 |
| NULL | 1792991 | 787689 | 16227033 | 44213344 | 47460670 | 61110822 | 7105143 | 60607 | 360765 | 4336 | 179123400 |
+-------+---------+--------+----------+----------+----------+----------+---------+-------+--------+------+-----------+
Notice how Alaska (AK) has populations in high latitudes and Hawaii (HI) in low latitudes.
Example 2 - Home Solar Power Generation
This give the power (KWh) generated by hour and month for 2012.
-- Sample input:
+---------------------+------+
| ts | enwh |
+---------------------+------+
| 2012-06-06 11:00:00 | 523 |
| 2012-06-06 11:05:00 | 526 |
| 2012-06-06 11:10:00 | 529 |
| 2012-06-06 11:15:00 | 533 |
| 2012-06-06 11:20:00 | 537 |
| 2012-06-06 11:25:00 | 540 |
| 2012-06-06 11:30:00 | 542 |
| 2012-06-06 11:35:00 | 543 |
Note that it is a reading in watts for each 5 minutes.
So, summing is needed to get the breakdown by month and hour.
-- Invoke the SP:
CALL Pivot('details', -- Table
'MONTH(ts)', -- `base_cols`, to put on left; SUM up over the month
'HOUR(ts)', -- `pivot_col` to put across the top; SUM up entries across the hour
'enwh/1000', -- The data -- watts converted to KWh
"WHERE ts >= '2012-01-01' AND ts < '2012-01-01' + INTERVAL 1 year", -- Limit to one year
''); -- assumes that the months stay in order
-- The SQL generated:
SELECT MONTH(ts),
SUM(IF(HOUR(ts) = "5", enwh/1000, 0)) AS "5",
SUM(IF(HOUR(ts) = "6", enwh/1000, 0)) AS "6",
SUM(IF(HOUR(ts) = "7", enwh/1000, 0)) AS "7",
SUM(IF(HOUR(ts) = "8", enwh/1000, 0)) AS "8",
SUM(IF(HOUR(ts) = "9", enwh/1000, 0)) AS "9",
SUM(IF(HOUR(ts) = "10", enwh/1000, 0)) AS "10",
SUM(IF(HOUR(ts) = "11", enwh/1000, 0)) AS "11",
SUM(IF(HOUR(ts) = "12", enwh/1000, 0)) AS "12",
SUM(IF(HOUR(ts) = "13", enwh/1000, 0)) AS "13",
SUM(IF(HOUR(ts) = "14", enwh/1000, 0)) AS "14",
SUM(IF(HOUR(ts) = "15", enwh/1000, 0)) AS "15",
SUM(IF(HOUR(ts) = "16", enwh/1000, 0)) AS "16",
SUM(IF(HOUR(ts) = "17", enwh/1000, 0)) AS "17",
SUM(IF(HOUR(ts) = "18", enwh/1000, 0)) AS "18",
SUM(IF(HOUR(ts) = "19", enwh/1000, 0)) AS "19",
SUM(IF(HOUR(ts) = "20", enwh/1000, 0)) AS "20",
SUM(enwh/1000) AS Total
FROM details WHERE ts >= '2012-01-01' AND ts < '2012-01-01' + INTERVAL 1 year GROUP BY MONTH(ts)
WITH ROLLUP
-- That generated decimal places that I did like:
| MONTH(ts) | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14
| 15 | 16 | 17 | 18 | 19 | 20 | Total |
+-----------+--------+---------+----------+----------+-----------+-----------+-----------+-----------+-----------+------
-----+-----------+----------+----------+----------+---------+--------+------------+
| 1 | 0.0000 | 0.0000 | 1.8510 | 21.1620 | 52.3190 | 73.0420 | 89.3220 | 97.0190 | 88.9720 | 75.
4970 | 50.9270 | 12.5130 | 0.5990 | 0.0000 | 0.0000 | 0.0000 | 563.2230 |
| 2 | 0.0000 | 0.0460 | 5.9560 | 35.6330 | 72.4710 | 96.5130 | 112.7770 | 126.0850 | 117.1540 | 96.
7160 | 72.5900 | 33.6230 | 4.7650 | 0.0040 | 0.0000 | 0.0000 | 774.3330 |
Other variations made the math go wrong. (Note that there is no CAST to FLOAT.)
While I was at it, I gave an alias to change "MONTH(ts)" to just "Month".
So, I edited the SQL to this and ran it:
SELECT MONTH(ts) AS 'Month', ROUND(SUM(IF(HOUR(ts) = "5", enwh, 0))/1000) AS "5", ... ROUND(SUM(IF(HOUR(ts) = "20", enwh, 0))/1000) AS "20", ROUND(SUM(enwh)/1000) AS Total FROM details WHERE ts >= '2012-01-01' AND ts < '2012-01-01' + INTERVAL 1 year GROUP BY MONTH(ts) WITH ROLLUP;
-- Which gave cleaner output:
+-------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+ | Month | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | Total | +-------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+ | 1 | 0 | 0 | 2 | 21 | 52 | 73 | 89 | 97 | 89 | 75 | 51 | 13 | 1 | 0 | 0 | 0 | 563 | | 2 | 0 | 0 | 6 | 36 | 72 | 97 | 113 | 126 | 117 | 97 | 73 | 34 | 5 | 0 | 0 | 0 | 774 | | 3 | 0 | 0 | 9 | 46 | 75 | 105 | 121 | 122 | 128 | 126 | 105 | 71 | 33 | 10 | 0 | 0 | 952 | | 4 | 0 | 1 | 14 | 63 | 111 | 146 | 171 | 179 | 177 | 158 | 141 | 105 | 65 | 26 | 3 | 0 | 1360 | | 5 | 0 | 4 | 21 | 78 | 128 | 162 | 185 | 199 | 196 | 187 | 166 | 130 | 81 | 36 | 8 | 0 | 1581 | | 6 | 0 | 4 | 17 | 71 | 132 | 163 | 182 | 191 | 193 | 182 | 161 | 132 | 89 | 43 | 10 | 1 | 1572 | | 7 | 0 | 3 | 17 | 57 | 121 | 160 | 185 | 197 | 199 | 189 | 168 | 137 | 92 | 44 | 11 | 1 | 1581 | | 8 | 0 | 1 | 11 | 48 | 104 | 149 | 171 | 183 | 187 | 179 | 156 | 121 | 76 | 32 | 5 | 0 | 1421 | | 9 | 0 | 0 | 6 | 32 | 77 | 127 | 151 | 160 | 159 | 148 | 124 | 93 | 47 | 12 | 1 | 0 | 1137 | | 10 | 0 | 0 | 1 | 16 | 54 | 85 | 107 | 115 | 119 | 106 | 85 | 56 | 17 | 2 | 0 | 0 | 763 | | 11 | 0 | 0 | 5 | 30 | 57 | 70 | 84 | 83 | 76 | 64 | 35 | 8 | 1 | 0 | 0 | 0 | 512 | | 12 | 0 | 0 | 2 | 17 | 39 | 54 | 67 | 75 | 64 | 58 | 31 | 4 | 0 | 0 | 0 | 0 | 411 | | NULL | 0 | 13 | 112 | 516 | 1023 | 1392 | 1628 | 1728 | 1703 | 1570 | 1294 | 902 | 506 | 203 | 38 | 2 | 12629 | +-------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+
Midday in the summer is the best time for solar panels, as you would expect. 1-2pm in July was the best.
Postlog
Posted, Feb. 2015
See Also
- Brawley's notes
Rick James graciously allowed us to use this article in the Knowledge Base.
Rick James' site has other useful tips, how-tos, optimizations, and debugging tips.
Original source: http://mysql.rjweb.org/doc.php/pivot
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
