Example 1 - Population vs Latitude in US
Example 2 - Home Solar Power Generation
The Problem
You want to "pivot" the data so that a linear list of values with
2 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.
Another Technique
Given (thanks to barraka and the now-defunct stackoverflow documentation):
create table tbl_values (
id int unsigned not null primary key,
`Name` varchar(10),
`Group` varchar(10),
`Value` int
);
insert into tbl_values values
(1, 'Pete', 'A', 10),
(2, 'Pete', 'B', 20),
(3, 'John', 'A', 10);
Do this:
-- 1. Create an expression that builds the columns
set @sql = (
select group_concat(distinct
concat(
"sum(case when `Group`='", `Group`, "' then `Value` end) as `", `Group`, "`"
)
)
from tbl_values
);
-- 2. Complete the SQL instruction
set @sql = concat("select Name, ", @sql, " from tbl_values group by `Name`");
-- 3. Create a prepared statement
prepare stmt from @sql;
-- 4. Execute the prepared statement
execute stmt;
Result:
Name A B
John 10 NULL
Pete 10 20
Stored Procedures return multiple result sets
For PDO, here are some links to explain "how to":
PHP manual
StackOverflow
tutorial
IBM
Postlog
Posted: Feb. 2015
Another technique: Sep, 2017
Pivoting in MariaDB (getting started)
Pivoting in MariaDB (reference manual)
Brawley's notes
Dynamically convert rows to columns
MySQL pivot table
MySQL dynamic column name
An example
Partition Maintenance (DROP+REORG) for time series (includes list of PARTITION uses)
Big DELETEs - how to optimize -- and other chunking advice, plus a use for PARTITIONing
Chunking lengthy DELETE/UPDATE/etc.
Data Warehouse techniques:
Data Warehouse Overview
Summary Tables
High speed ingestion
Bulk Normalization
Schema and code design for large Sensor database
Entity-Attribute-Value (EAV) -- a common, poorly performing, design pattern; plus an alternative
Lat/Lng search to Find the nearest 10 pizza parlors
Lat/Long representation choices
Pagination, not with OFFSET, LIMIT
Techniques on efficiently finding a random row (On beyond ORDER BY RAND())
GUID/UUID Performance (type 1 only)
IP Range Table Performance -- or other disjoint ranges
Alter of a Huge table -- Mostly obviated by 5.6
Efficient List of Latest 10 news articles
Build and execute a Pivot SELECT (showing rows as columns)
(Groupwise Max): Efficiently find largest row(s) for each group
Other Tips, Tuning, Debugging, Optimizations, etc...
Rick's RoTs (Rules of Thumb -- lots of tips)
Datatypes and building a good schema
Memory Allocation (caching, etc)
Character Set and Collation problem solver
Trouble with UTF-8
If you want case folding, but accent sensitivity, please file a request at https://bugs.mysql.com .
Python tips,
PHP tips,
other language tips
utf8 Collations
utf8mb4 Collations on 8.0
Converting from MyISAM to InnoDB -- includes differences between them
Compound INDEXes plus other insights into the mysteries of INDEXing
Cookbook for Creating Indexes
Many-to-many mapping table
Handler counts
wp_postmeta
UNION+OFFSET
MySQL Limits -- built-in hard limits
767-byte INDEX limit
Galera, tips on converting to (Percona XtraDB Cluster, MariaDB 10, or manually installed)
5.7's Query Rewrite -- perhaps 5.7's best perf gain, at least for this forum's users
Analyze MySQL Performance
Analyze VARIABLEs and GLOBAL STATUS
Analyze SlowLog
My slides from conferences
MiniFest 2021 - Rick James & Daniel Black - Answering on Stack Overflow(+comments) - MariaDB Frontlines
Percona Live 4/2017 - Rick's RoTs (Rules of Thumb) - MySQL/MariaDB
Percona Live 4/2017 - Index Cookbook - MySQL/MariaDB
Percona Live 9/2015 - PARTITIONing - MySQL/MariaDB
Contact me via LinkedIn; be sure to include a brief teaser in the Invite request:
