Thread: a simple transform

a simple transform

From
"Karl Grossner"
Date:
I've thrashed at this transform for quite a while and come up empty. The
crosstab() functions, and the documented examples, all do something more
complex than I need. I can do this after the fact trivially in python with
the 'zip()' function, but I need it real-time from a view or function.

Of course I could be overlooking the obvious (it's happened before!)

I have this:attr1        attr2        attr3------    ------    ------
cat1    0.34        0.12        0.22
cat2    0.11        0.67        0.31

and need it transposed thus:    cat1        cat2    -----        -----
attr1        0.34        0.11
attr2        0.12        0.67
attr3        0.22        0.31

in case it adds motivation (;^/), cat1 and cat2 are democrat & republican;
the attr's are campaign issues; the values are "aboutness" derived from a
text similarity measure

tia
Karl



Re: a simple transform

From
Frank Bax
Date:
Karl Grossner wrote:
> I've thrashed at this transform for quite a while and come up empty. The
> crosstab() functions, and the documented examples, all do something more
> complex than I need. I can do this after the fact trivially in python with
> the 'zip()' function, but I need it real-time from a view or function.
> 
> Of course I could be overlooking the obvious (it's happened before!)
> 
> I have this:
>     attr1        attr2        attr3
>     ------    ------    ------
> cat1    0.34        0.12        0.22
> cat2    0.11        0.67        0.31
> 
> and need it transposed thus:
>         cat1        cat2
>         -----        -----
> attr1        0.34        0.11
> attr2        0.12        0.67
> attr3        0.22        0.31


I see no response; so I thought I'd take a stab at it.  I don't have 8.3 
yet so I cannot test my idea.  I read the docs, and I think what you 
need to do is create SQL that will produce

cat  attr   val
---- ----- ----
cat1 attr1 0.34
cat1 attr2 0.12
cat1 attr3 0.22
cat2 attr1 0.11
cat2 attr2 0.67
cat2 attr3 0.31

That pass the SQL into crosstab().


Re: a simple transform

From
"Richard Broersma"
Date:
On Tue, Sep 16, 2008 at 6:37 AM, Frank Bax <fbax@sympatico.ca> wrote:
>
> That pass the SQL into crosstab().

It might be interesting to look at this blog also:

http://okbob.blogspot.com/#7449458148004287481


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug