Re: Normalized storage to denormalized report

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