Fred S wrote:
Hi,
I am working on a database of time series, where the main table looks
like this
date | id | value
but i have something like several thousands of id's and several
thousands of dates as well
my goal is to obtain
date in the format of a matrix with the rows being the dates and each
column contains the values for a certain ID.
Now obviously I could write a join statement, but i dont think that its
the best way.
I there a way to dynamiccaly create these joins ? maybe using a stored
procedure.
See the article here:
http://dev.mysql.com/tech-resources/...ard/index.html
So you could make a quey such as this one:
SELECT t.date,
GROUP_CONCAT(IF(t.id = 1, t.value, NULL)) AS `ID 1`,
GROUP_CONCAT(IF(t.id = 2, t.value, NULL)) AS `ID 2`,
GROUP_CONCAT(IF(t.id = 3, t.value, NULL)) AS `ID 3`
FROM mytable AS t
GROUP BY t.date, t.id
Unfortunately, this solution for the crosstab query in MySQL requires
that you hard-code the columns. This is in part because there's no way
to make a SQL query have a dynamic number of columns, or to use a
variable in a column alias.
You can run a query prior to this, to get a list of distinct id values,
and then in your application dynamically construct the query as a
string, then execute it.
You may have to choose a subset of id values, because I don't think you
can have an unlimited number of fields in a select-list. I could only
find a reference that MyISAM tables can have a max number of columns of
3392, but this doesn't mean that the same limit applies to fields in a
query.
Regards,
Bill K.
