big query question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

Many Thanks


Re: big query question

in google search for oracle pivot table

Re: big query question

also mysql pivot table :)

Re: big query question

also mysql pivot table :)

Re: big query question

Fred S wrote:
Quoted text here. Click to load it

See the article here:

So you could make a quey such as this one:

   GROUP_CONCAT(IF( = 1, t.value, NULL)) AS `ID 1`,
   GROUP_CONCAT(IF( = 2, t.value, NULL)) AS `ID 2`,
   GROUP_CONCAT(IF( = 3, t.value, NULL)) AS `ID 3`
FROM mytable AS t

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

Bill K.

Site Timeline