# MySQL: Columns as Ordered Week Dates

Let’s say you have data containing some metrics and their values across an ordered set of dates in a week. Since most screens are longer horizontally than vertically, it’s sometimes better to present data where one metric lies in a row and the dates lie in columns, rather than the usual way around.

The usual way we show tables is like this:

date Visitors Orders Revenue Metric4 etc.
2016-02-28 1423 19 900
2016-02-29 1534 38 2037
2016-03-01 2645 57 5612

Because most screens are in landscape mode and because we read from left to right, there are times when it makes sense to pivot the table as follows:

metric 2016-02-28 2016-02-29 2016-03-01
Visitors 1423 1534 2645
Orders 19 38 57
Revenue 900 2037 5612
Metric4
Metric5
etc.

This may not be “tidy data” as defined by Hadley Wickham in his excellent paper, but pivoting as such results in easier navigation/scrolling when you have more metrics than dates.

For example, let’s say you have a weekly report with 7 consecutive dates from Monday to Sunday of that week, and 30 metrics. You’ve seen the 7-day trend on the first metric, and would like to see the trend on the 27th metric. All you need to do is scroll down to the 27th metric, and read as you would (from left to right), to see if there’s any trend seen across that week.

Here’s the full MySQL query for the pivot table above, where the rows are metrics and the columns comprise of an ordered set of dates in a week.

The rank variable (an integer) allows you to show the metrics in a specific order. A metric with a certain rank will be shown before another metric with a higher numbered rank.

Hope this helps!

MySQL: Columns as Ordered Week Dates - March 8, 2016 - Adler Santos