0

Query I have now:

SELECT DATE(requestdate), HOUR(requestdate), count(*)
FROM timeseries
GROUP BY DATE(requestdate), HOUR(requestdate)
ORDER BY requestdate

Returns eg:

2018-10-12   0    7655
2018-10-12   1    876
2018-10-12   2    1344
...
2018-10-12   23   1009
...
2018-10-13   0    765
2018-10-13   1    9879
2018-10-13   2    2132
...
2018-10-13   23   876
... more days

Desired result eg:

     2018-10-12     2018-10-13     ...
0    7655           765
1    876            9879
2    1344           2132
...
23   1009           876

How would I do this? (don't need a particularly performant solution)

iss42
  • 2,720
  • 3
  • 21
  • 37
  • 1
    The SQL language has a strict requirement that the number of types of columns can be determined _at query compile time_. If you want the columns to be determined by the data at execution time, as in this question, **you will not be able to do it in a single SQL statement.** There's just no way. The only thing you can do is write one query to get a list of the dates you need, and use those results to build another query dynamically. Or you can use your query in the question, and let the client language pivot the data. That's usually better practice anyway. – Joel Coehoorn Oct 18 '18 at 00:47
  • Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Madhur Bhaiya Oct 18 '18 at 04:27
  • Always consider handling data display related things in your application code layer (eg: PHP, C++, Java) etc – Madhur Bhaiya Oct 18 '18 at 04:28

0 Answers0