0

I'm trying to dump values from a database into a spreadsheet with key values as columns.

Here's the basic query I'm trying to perform. It finds the average values of every company for every quarter.

SELECT company_name, CONCAT(YEAR(date), "Q", QUARTER(date)) date, AVG(value)
FROM company_values
JOIN companies
ON company_values.company_id=companies.company_id
GROUP BY company_name, YEAR(date), QUARTER(date)
ORDER BY date ASC;

It outputs a table like you would expect:

+--------------+--------+------------+
| company_name |  date  | AVG(value) |
+--------------+--------+------------+
| Yahoo        | 2009Q1 |      0.613 |
| Facebook     | 2009Q1 |      0.724 |
| Motorola     | 2009Q1 |      0.926 |
| Yahoo        | 2009Q2 |      0.854 |
| Facebook     | 2009Q2 |      0.094 |
| Motorola     | 2009Q2 |      0.025 |
| Yahoo        | 2009Q3 |      0.193 |
| Facebook     | 2009Q3 |      0.074 |
| Motorola     | 2009Q3 |      0.499 |
+--------------+--------+------------+

It works well enough, but I need to put each company name as its own column, like this:

+--------+----------+----------+-------+
|  date  | Facebook | Motorola | Yahoo |
+--------+----------+----------+-------+
| 2009Q1 |    0.724 |    0.926 | 0.724 |
| 2009Q2 |    0.094 |    0.854 | 0.025 |
| 2009Q3 |    0.074 |    0.499 | 0.193 |
+--------+----------+----------+-------+

Now for the hard part: there are hundreds of companies. I know this is a rather difficult task for MySQL, but I don't know how else to do this. I don't care about execution time; it can run all night.

Any ideas?

Drew
  • 24,851
  • 10
  • 43
  • 78
Rachie
  • 433
  • 1
  • 6
  • 17
  • 1
    That there is a very neatly drawn table. Must have taken you a lot of time. – aMazing Nov 12 '15 at 02:41
  • 2
    You know know the specific companies, then this is a pivot. Google: "mysql pivot". If you want a variable number of columns, then Google "mysql dynamic pivot". – Gordon Linoff Nov 12 '15 at 02:43

0 Answers0