Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- big query question
April 18, 2006, 8:23 pm
rate this thread
I am working on a database of time series, where the main table looks
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
Re: big query question
Fred S wrote:
See the article here:
So you could make a quey such as this one:
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
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum