While reading in a fantastic SQL book I frequent quite a bit, I discovered a type of query in one of the examples that I really should know. For a better learning experience, in order to solidly the concepts presented, I applied them to some data that I am familiar with. The topic: pivoting rows of data. Although I am aware of this data presentation, I have never studied a pivot query. ‘So why not learn it then’ I thought to myself. Are you interested in learning about a pivot query also? Continue reading and see an easy-to-comprehend example…
OS and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.20
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
To start, we will visit this CREATE VIEW statement, which forms the base data used in this post:
1 2 3 4 | MySQL localhost:33060+ ssl walking SQL > CREATE VIEW vw_hot_hikes AS -> SELECT day_walked, cal_burned -> FROM walking_stats -> WHERE MONTHNAME(day_walked) IN ('June','July','August'); |
Below, is a more detailed output using the SHOW command:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl walking SQL > SHOW CREATE VIEW vw_hot_hikes\G *************************** 1. row *************************** View: vw_hot_hikes Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`j2112o`@`localhost` SQL SECURITY DEFINER VIEW `vw_hot_hikes` AS select `walking_stats`.`day_walked` AS `day_walked`,`walking_stats`.`cal_burned` AS `cal_burned` from `walking_stats` where (monthname(`walking_stats`.`day_walked`) in ('June','July','August')) character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci 1 row in set (0.0009 sec) |
The ‘vw_hot_hikes’ VIEW provides personal walking data (I collect via a mobile app) for 3 months: June, July, and August – some of the hottest where I am in the southern United States temperature-wise. I came up with this simple requirement – or scenario – to compare how many days I walked during this three-month span, and burned over 300 calories. Likely not a very useful metric in the real world. But, great for learning (for me).
There are a total of 71 rows present in the ‘vw_hot_hikes’ VIEW:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl walking SQL > SELECT COUNT(*) FROM vw_hot_hikes; +----------+ | COUNT(*) | +----------+ | 71 | +----------+ 1 row in set (0.0015 sec) |
This query does, in fact, return a result set based on the criteria – the count of days I burned over 300 calories during a walk in the range of the specified 3-month span, grouped for each month:
1 2 3 4 5 6 7 8 9 10 11 12 | MySQL localhost:33060+ ssl walking SQL > SELECT MONTHNAME(day_walked), COUNT(cal_burned) -> FROM vw_hot_hikes -> WHERE cal_burned > 300 -> GROUP BY MONTHNAME(day_walked); +-----------------------+-------------------+ | MONTHNAME(day_walked) | COUNT(cal_burned) | +-----------------------+-------------------+ | June | 9 | | July | 24 | | August | 21 | +-----------------------+-------------------+ 3 rows in set (0.0018 sec) |
A pivot query basically presents a rows’ worth of data as a column. An example – and the end goal of this posts’ material – is shown below (aside from the column naming difference):
1 2 3 4 5 6 | +----------+----------+---------+ | june_300 | july_300 | aug_300 | +----------+----------+---------+ | 9 | 24 | 21 | +----------+----------+---------+ 1 row in set (0.0351 sec) |
So, how do we transform the rows to this format?
This first query is almost correct. Allow me to rephrase that. The results are correct, just not presented in the pivot display format.
Notice in those instances where a rows’ column value does not apply to one of the target months (June, July, or August), there is a 0 (zero):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MySQL localhost:33060+ ssl walking SQL > SELECT SUM(CASE WHEN MONTHNAME(day_walked) = 'June' THEN 1 ELSE 0 END) AS june_300, -> SUM(CASE WHEN MONTHNAME(day_walked) = 'July' THEN 1 ELSE 0 END) AS july_300, -> SUM(CASE WHEN MONTHNAME(day_walked) = 'August' THEN 1 ELSE 0 END) AS aug_300 -> FROM vw_hot_hikes -> WHERE cal_burned > 300 -> GROUP BY MONTHNAME(day_walked); +----------+----------+---------+ | june_300 | july_300 | aug_300 | +----------+----------+---------+ | 9 | 0 | 0 | | 0 | 24 | 0 | | 0 | 0 | 21 | +----------+----------+---------+ 3 rows in set (0.0020 sec) |
Again, these are correct results, yet, we still would like to remove the 0’s (zeros) from the result set and transform it into a single row.
Firstly, how does the query work? The SUM() aggregate function and the CASE expression are the keys.
CASE basically provides the rows to columns transformation in this specific example. Supplying the ‘day_walked’ column as the argument to the MONTHNAME() function returns – you guessed it – a month name value. The WHEN clause evaluates this expression and when it is true (equal) to either: ‘June’, ‘July’, or ‘August’ respectively, the THEN clause executes, counting (horribly termed here – my apologies) it as 1 (one). Should there be no match, the ELSE clause executes, assigning that row a 0 (zero). SUM() totals up all of the individual values returned by CASE. Because any expression evaluated by WHEN that is true receives a 1 (one), that is how the total number is determined for each respective month based on the WHERE clause predicate of cal_burned greater than 300.
In order to drop those 0’s (zeros), we simply need to remove the GROUP BY clause from the query:
1 2 3 4 5 6 7 8 9 10 11 | MySQL localhost:33060+ ssl walking SQL > SELECT SUM(CASE WHEN MONTHNAME(day_walked) = 'June' THEN 1 ELSE 0 END) AS june_300, -> SUM(CASE WHEN MONTHNAME(day_walked) = 'July' THEN 1 ELSE 0 END) AS july_300, -> SUM(CASE WHEN MONTHNAME(day_walked) = 'August' THEN 1 ELSE 0 END) AS aug_300 -> FROM vw_hot_hikes -> WHERE cal_burned > 300; +----------+----------+---------+ | june_300 | july_300 | aug_300 | +----------+----------+---------+ | 9 | 24 | 21 | +----------+----------+---------+ 1 row in set (0.0351 sec) |
How easy was that?!? We have the pivoted row of data.
I hope that in learning how to pivot a query result into a single row and sharing on my blog, that other’s seeking information on this same type of requirement, find this post a valuable resource. Share it along!
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.