Thread: Normalized storage to denormalized report

I have a table that stores data like so:

Create table raw_vals (
    expt_id int,
    clone_idx int,
    val numeric,
    primary key (expt_id,clone_idx)
);

And I would like to design a query that gives:

Clone_idx   expt_id_1  expt_id_2 ....
1           0.7834     0.8231    ....
2           0.2832     1.2783    ....

There are several tens-of-thousands of clone_idx entries, so doing this
quickly may not even be possible.  Any suggestions on how to go from this
normalized structure to the denormalized form easily.   Note that this isn't
exactly a crosstab, since there is no counting data, just reshaping.

Thanks,
Sean

On Thu, Jan 19, 2006 at 07:03:47AM -0500, Sean Davis wrote:
> I have a table that stores data like so:

<snip>

> And I would like to design a query that gives:
>
> Clone_idx   expt_id_1  expt_id_2 ....
> 1           0.7834     0.8231    ....
> 2           0.2832     1.2783    ....
>
> There are several tens-of-thousands of clone_idx entries, so doing this
> quickly may not even be possible.  Any suggestions on how to go from this
> normalized structure to the denormalized form easily.   Note that this isn't
> exactly a crosstab, since there is no counting data, just reshaping.

Although you may not call it exactly a crosstab, the crosstab
functions in contrib/tablefunc should solve your problem nicely.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Jan 19, 2006, at 21:03 , Sean Davis wrote:

> I have a table that stores data like so:
>
> Create table raw_vals (
>     expt_id int,
>     clone_idx int,
>     val numeric,
>     primary key (expt_id,clone_idx)
> );
>
> And I would like to design a query that gives:
>
> Clone_idx   expt_id_1  expt_id_2 ....
> 1           0.7834     0.8231    ....
> 2           0.2832     1.2783    ....
>
> There are several tens-of-thousands of clone_idx entries, so doing
> this
> quickly may not even be possible.  Any suggestions on how to go
> from this
> normalized structure to the denormalized form easily.   Note that
> this isn't
> exactly a crosstab, since there is no counting data, just reshaping.

As far as I know, the crosstab functions in contrib/tablefunc will
still do what you want. Another way is to have as many (self) joins
as you want columns.

Michael Glaesemann
grzm myrealbox com

On 1/19/06 7:14 AM, "Martijn van Oosterhout" <kleptog@svana.org> wrote:

> On Thu, Jan 19, 2006 at 07:03:47AM -0500, Sean Davis wrote:
>> I have a table that stores data like so:
>
> <snip>
>
>> And I would like to design a query that gives:
>>
>> Clone_idx   expt_id_1  expt_id_2 ....
>> 1           0.7834     0.8231    ....
>> 2           0.2832     1.2783    ....
>>
>> There are several tens-of-thousands of clone_idx entries, so doing this
>> quickly may not even be possible.  Any suggestions on how to go from this
>> normalized structure to the denormalized form easily.   Note that this isn't
>> exactly a crosstab, since there is no counting data, just reshaping.
>
> Although you may not call it exactly a crosstab, the crosstab
> functions in contrib/tablefunc should solve your problem nicely.
>
> Have a nice day,

Thanks Martijn and Michael.  Crosstab functions will do the trick (though
for production, I think I will have to use some materialized views....)

Sean