time series data storage

Hi all, I am trying to get my head around changing the format of some
data for import into a simulation tool.
I am storing arisings values for several machines for several years.

The data is in a table like this:

id name year value
1 machine 1 2008 10
2 machine 2 2008 23
3 machine 3 2008 34
4 machine 1 2009 120
5 machine 3 2009 233
6 machine 1 2010 292
7 machine 2 2010 34
8 machine 3 2010 102

I need to be able to output it like this:
2008 2009 2010
machine 1 10 120 292
machine 2 23 NULL 34
machine 3 34 233 102

I have searched the net, but think im looking for the wrong terms,
this must be possible with some funky inner join thing, but I have to
admit to being a bit stumped.

Can anyone shed any light on this?

Cheers
Dan

On 4 Feb, 15:52, Dan Braun <danbra...@gmail.com> wrote:
> Hi all, I am trying to get my head around changing the format of some
> data for import into a simulation tool.
> I am storing arisings values for several machines for several years.
>
> The data is in a table like this:
>
> id name year value
> 1 machine 1 2008 10
> 2 machine 2 2008 23
> 3 machine 3 2008 34
> 4 machine 1 2009 120
> 5 machine 3 2009 233
> 6 machine 1 2010 292
> 7 machine 2 2010 34
> 8 machine 3 2010 102
>
> I need to be able to output it like this:
> 2008 2009 2010
> machine 1 10 120 292
> machine 2 23 NULL 34
> machine 3 34 233 102
>
> I have searched the net, but think im looking for the wrong terms,
> this must be possible with some funky inner join thing, but I have to
> admit to being a bit stumped.
>
> Can anyone shed any light on this?
>
> Cheers
> Dan

Something like:
SELECT
a1.name,
a1.value 2008,
a2.value 2009,
a3.value 2010
FROM table a1
LEFT JOIN table a2 ON a1.name = a2.name AND a2.year = '2009'
LEFT JOIN table a3 ON a1.name = a3.name AND a3.year = '2010'
WHERE a1.year = '2008'

On 4 Feb, 16:37, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 4 Feb, 15:52, Dan Braun <danbra...@gmail.com> wrote:
>
>
>
>
>
> > Hi all, I am trying to get my head around changing the format of some
> > data for import into a simulation tool.
> > I am storing arisings values for several machines for several years.
>
> > The data is in a table like this:
>
> > id name * * * *year *value
> > 1 machine 1 *2008 10
> > 2 machine 2 *2008 23
> > 3 machine 3 *2008 34
> > 4 machine 1 *2009 120
> > 5 machine 3 *2009 233
> > 6 machine 1 *2010 292
> > 7 machine 2 *2010 34
> > 8 machine 3 *2010 102
>
> > I need to be able to output it like this:
> > * * * * * * * * *2008 *2009 *2010
> > machine 1 *10 * * *120 * *292
> > machine 2 *23 * * NULL * 34
> > machine 3 *34 * * *233 * *102
>
> > I have searched the net, but think im looking for the wrong terms,
> > this must be possible with some funky inner join thing, but I have to
> > admit to being a bit stumped.
>
> > Can anyone shed any light on this?
>
> > Cheers
> > Dan
>
> Something like:
> SELECT
> * a1.name,
> * a1.value 2008,
> * a2.value 2009,
> * a3.value 2010
> FROM table a1
> LEFT JOIN table a2 ON a1.name = a2.name AND a2.year = '2009'
> LEFT JOIN table a3 ON a1.name = a3.name AND a3.year = '2010'
> WHERE a1.year = '2008'- Hide quoted text -
>
> - Show quoted text -

hi! thats what I cam up with, the trouble is that the actual dataset
is for 30 odd machines for 160 years, so the sql would look
gargantuan, I wondered if there was a way of doing it recursively, or
generating rows like
10 120 292 as some kind of subquery (select year from
datatable where name = (select distinct name from datatable))
is this possible?

I wonder if I might have overnormalised the data somewhat, but quite a
few of the machines wont have arisings for several years, leaving lots
of whitespace in a table where each machine has a column for every
year.

Dan

On 4 Feb, 17:09, Dan Braun <danbra...@gmail.com> wrote:
> On 4 Feb, 16:37, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 4 Feb, 15:52, Dan Braun <danbra...@gmail.com> wrote:
>
> > > Hi all, I am trying to get my head around changing the format of some
> > > data for import into a simulation tool.
> > > I am storing arisings values for several machines for several years.
>
> > > The data is in a table like this:
>
> > > id name * * * *year *value
> > > 1 machine 1 *2008 10
> > > 2 machine 2 *2008 23
> > > 3 machine 3 *2008 34
> > > 4 machine 1 *2009 120
> > > 5 machine 3 *2009 233
> > > 6 machine 1 *2010 292
> > > 7 machine 2 *2010 34
> > > 8 machine 3 *2010 102
>
> > > I need to be able to output it like this:
> > > * * * * * * * * *2008 *2009 *2010
> > > machine 1 *10 * * *120 * *292
> > > machine 2 *23 * * NULL * 34
> > > machine 3 *34 * * *233 * *102
>
> > > I have searched the net, but think im looking for the wrong terms,
> > > this must be possible with some funky inner join thing, but I have to
> > > admit to being a bit stumped.
>
> > > Can anyone shed any light on this?
>
> > > Cheers
> > > Dan
>
> > Something like:
> > SELECT
> > * a1.name,
> > * a1.value 2008,
> > * a2.value 2009,
> > * a3.value 2010
> > FROM table a1
> > LEFT JOIN table a2 ON a1.name = a2.name AND a2.year = '2009'
> > LEFT JOIN table a3 ON a1.name = a3.name AND a3.year = '2010'
> > WHERE a1.year = '2008'- Hide quoted text -
>
> > - Show quoted text -
>
> hi! thats what I cam up with, the trouble is that the actual dataset
> is for 30 odd machines for 160 years, so the sql would look
> gargantuan, I wondered if there was a way of doing it recursively, or
> generating rows like
10 * * *120 * *292 as some kind of subquery (select value from
datatable where date = (select distinct date from datatable))

> is this possible?
>
> I wonder if I might have overnormalised the data somewhat, but quite a
> few of the machines wont have arisings for several years, leaving lots
> of whitespace in a table where each machine has a column for every
> year.
>
> Dan- Hide quoted text -
>
> - Show quoted text -

On Feb 4, 12:09 pm, Dan Braun <danbra...@gmail.com> wrote:
> On 4 Feb, 16:37, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 4 Feb, 15:52, Dan Braun <danbra...@gmail.com> wrote:
>
> > > Hi all, I am trying to get my head around changing the format of some
> > > data for import into a simulation tool.
> > > I am storing arisings values for several machines for several years.
>
> > > The data is in a table like this:
>
> > > id name year value
> > > 1 machine 1 2008 10
> > > 2 machine 2 2008 23
> > > 3 machine 3 2008 34
> > > 4 machine 1 2009 120
> > > 5 machine 3 2009 233
> > > 6 machine 1 2010 292
> > > 7 machine 2 2010 34
> > > 8 machine 3 2010 102
>
> > > I need to be able to output it like this:
> > > 2008 2009 2010
> > > machine 1 10 120 292
> > > machine 2 23 NULL 34
> > > machine 3 34 233 102
>
> > > I have searched the net, but think im looking for the wrong terms,
> > > this must be possible with some funky inner join thing, but I have to
> > > admit to being a bit stumped.
>
> > > Can anyone shed any light on this?
>
> > > Cheers
> > > Dan
>
> > Something like:
> > SELECT
> > a1.name,
> > a1.value 2008,
> > a2.value 2009,
> > a3.value 2010
> > FROM table a1
> > LEFT JOIN table a2 ON a1.name = a2.name AND a2.year = '2009'
> > LEFT JOIN table a3 ON a1.name = a3.name AND a3.year = '2010'
> > WHERE a1.year = '2008'- Hide quoted text -
>
> > - Show quoted text -
>
> hi! thats what I cam up with, the trouble is that the actual dataset
> is for 30 odd machines for 160 years, so the sql would look
> gargantuan, I wondered if there was a way of doing it recursively, or
> generating rows like
> 10 120 292 as some kind of subquery (select year from
> datatable where name = (select distinct name from datatable))
> is this possible?
>
> I wonder if I might have overnormalised the data somewhat, but quite a
> few of the machines wont have arisings for several years, leaving lots
> of whitespace in a table where each machine has a column for every
> year.
>
> Dan

I would recommend doing the "cross-tab" in something other than SQL.
If you don't know how many years you're going to have then you've
designed your tables correctly. It's just that SQL isn't all that
great at doing this sort of thing. I don't know what you're doing
with this data, but if you're using something like PHP it would be
simple enough to do it there.

On Feb 4, 7:02 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Feb 4, 12:09 pm, Dan Braun <danbra...@gmail.com> wrote:
>
>
>
> > On 4 Feb, 16:37, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 4 Feb, 15:52, Dan Braun <danbra...@gmail.com> wrote:
>
> > > > Hi all, I am trying to get my head around changing the format of some
> > > > data for import into a simulation tool.
> > > > I am storing arisings values for several machines for several years.
>
> > > > The data is in a table like this:
>
> > > > id name year value
> > > > 1 machine 1 2008 10
> > > > 2 machine 2 2008 23
> > > > 3 machine 3 2008 34
> > > > 4 machine 1 2009 120
> > > > 5 machine 3 2009 233
> > > > 6 machine 1 2010 292
> > > > 7 machine 2 2010 34
> > > > 8 machine 3 2010 102
>
> > > > I need to be able to output it like this:
> > > > 2008 2009 2010
> > > > machine 1 10 120 292
> > > > machine 2 23 NULL 34
> > > > machine 3 34 233 102
>
> > > > I have searched the net, but think im looking for the wrong terms,
> > > > this must be possible with some funky inner join thing, but I have to
> > > > admit to being a bit stumped.
>
> > > > Can anyone shed any light on this?
>
> > > > Cheers
> > > > Dan
>
> > > Something like:
> > > SELECT
> > > a1.name,
> > > a1.value 2008,
> > > a2.value 2009,
> > > a3.value 2010
> > > FROM table a1
> > > LEFT JOIN table a2 ON a1.name = a2.name AND a2.year = '2009'
> > > LEFT JOIN table a3 ON a1.name = a3.name AND a3.year = '2010'
> > > WHERE a1.year = '2008'- Hide quoted text -
>
> > > - Show quoted text -
>
> > hi! thats what I cam up with, the trouble is that the actual dataset
> > is for 30 odd machines for 160 years, so the sql would look
> > gargantuan, I wondered if there was a way of doing it recursively, or
> > generating rows like
> > 10 120 292 as some kind of subquery (select year from
> > datatable where name = (select distinct name from datatable))
> > is this possible?
>
> > I wonder if I might have overnormalised the data somewhat, but quite a
> > few of the machines wont have arisings for several years, leaving lots
> > of whitespace in a table where each machine has a column for every
> > year.
>
> > Dan
>
> I would recommend doing the "cross-tab" in something other than SQL.
> If you don't know how many years you're going to have then you've
> designed your tables correctly. It's just that SQL isn't all that
> great at doing this sort of thing. I don't know what you're doing
> with this data, but if you're using something like PHP it would be
> simple enough to do it there.

the data is being presented in php, but also read into a simulation
tool, which is where i started having problems with it as the easiest
way to feed it into the model would be to sort of reconstitute the
data back into a table as i laid it out above. I will have to have a
more careful think about a set of big loops or something to populate
this table.

thanks for the help.

Dan