Re: a simple transform

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: a simple transform
Date: 2008-09-16 13:37:37
Message-ID: 48CFB6A1.7040706@sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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().

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma 2008-09-16 14:59:20 Re: surrogate vs natural primary keys
Previous Message Guillaume Lelarge 2008-09-16 09:39:17 Re: Simple Problem ?