Performing an arbitrary number of self joins in mySQL

Question

I have a dataset in mysql that contains the results of bandwidth tests for multiple named interfaces, with the date the test was performed and the name of the interface. A sample dataset would look something like this:

|  date  |  testResult  |  interface  |
---------------------------------------
| 12/25  |     32       |    eth0     |
| 12/25  |     21       |    eth0     |
| 12/25  |     25       |    eth0     |
| 12/26  |     30       |    eth0     |
| 12/27  |     33       |    eth0     |
| 12/25  |     12       |    eth1     |
| 12/25  |     16       |    eth1     |
| 12/27  |     3        |    vz0      |
| 12/26  |     120      |    virt1    |

I need to chart the average result for each interface on a given day, so my current solution is

SELECT `date`, AVG(`testResult`) as avg, `interface` FROM `tests` WHERE 1=1 
GROUP BY date, interface ORDER BY interface, date

This gives me a result like

|  date  |     avg      |  interface  |
---------------------------------------
| 12/25  |     26       |    eth0     |
| 12/26  |     30       |    eth0     |
| 12/27  |     33       |    eth0     |
| 12/25  |     14       |    eth1     |
| 12/26  |     120      |    virt1    |
| 12/27  |     3        |    vz0      |

My problem is that I need this data "joined" (I think) on the date, with a column for each interface's average on that day. The name and number of interfaces in a result set is not constant and can't be hardcoded. My ideal result set would look like this:

|  date  |  avg_eth0    |  avg_eth1   |  avg_virt1   |  avg_vz0   |
-------------------------------------------------------------------
| 12/25  |     26       |      14     |    NULL      |    NULL    |
| 12/26  |     30       |      NULL   |    120       |    NULL    |
| 12/27  |     33       |      NULL   |    NULL      |    3       |

Is there a way to perform this join, basically creating a column for each unique value of the interface column?

Cross tab's aren't really possible in mysql, you need application level code. See [also this answer](http://stackoverflow.com/questions/8977855/mysql-dynamic-cross-tab) — Wrikken, Sep 25 '13 at 17:50
can you generate the query and then execute it? there are very formulaic queries that would do this but you would need to query which values were there first.. or just use a pivot table in excel ;) — , Sep 25 '13 at 18:03

score 0 · Answer 1 · answered Sep 25 '13 at 17:56

I would advise to use pivoting:

select date,
AVG(case when interface='eth0' then avg else 0)) as eth0,
AVG(case when interface='eth0' then avg else 0)) as eth1,
AVG(case when interface='virt1' then avg else 0)) as virt1,
AVG(case when interface='vz0' then avg else 0)) as vz0
from (
SELECT `date`, AVG(`testResult`) as avg, `interface` FROM `tests` WHERE 1=1 
GROUP BY date, interface ORDER BY interface, date) as z group by date

score 0 · Answer 2 · answered Sep 25 '13 at 21:16

You can't generate a pivot-table query in any flavor of SQL without knowing the distinct values for the columns in the pivot.

In other words, you must hard-code one column per interface as shown in the answer from @David Jashi.

But that means you need to know the distinct values for interfaces first, before you can write the SQL query. You could do it pretty simply by running a query for that as a first step:

SELECT DISTINCT interface FROM `tests`;

And then loop over the result, appending more columns to an SQL query to do the pivot.

Sorry, there is no way for SQL to query for interfaces and also in the same query add more columns dynamically as it encounters distinct values in the data. You need to run two queries.

Performing an arbitrary number of self joins in mySQL

2 Answers2