Thread: Is it possible...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi all,

I wasn't able to get a usable answer by googling: Is there a way to create a
view on a table that converts rows to columns ?
Example:
I have a table

create table blah (
    id int4 serial,
    parentid int4,
    pname varchar(64),
    pvalue varchar(128)
);

which is basically a classic key/value pair. Parentid is a field defining that
certain rows belong to the same entity, i.e.:

1  1  'firstkey' 'firstvalue'
2  1  'secondkey' 'secondvalue'
...
...
10 2 'firstkey' 'firstvalue'
...

All the "parents" have the same set of keys - guaranteed.
I'd need to convert this into a table which has the  keynames as columns and
one row per "parentid", i.e.:

parentid firstkey     secondkey
1            firstvalue  secondvalue
2            firstvalue  secondvalue


I hate the idea of writing code that really creates and fills a table,
particularly since the original table has a lot of rows.

Is the above doable with a view?
Any pointers will be appreciated.

Thanks

    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBK2MfjqGXBvRToM4RAt/dAKCcBaDVMvfP9fIJDy6qFh8euRfnJwCfY8HH
hqjmfHNjzTjZ71jdhjv4cIU=
=TfAU
-----END PGP SIGNATURE-----

Uwe C. Schroeder wrote:
> parentid firstkey     secondkey
> 1            firstvalue  secondvalue
> 2            firstvalue  secondvalue
>
>
> I hate the idea of writing code that really creates and fills a table,
> particularly since the original table has a lot of rows.
>
> Is the above doable with a view?
> Any pointers will be appreciated.

You can do what you want with set returning functions in
contrib/tablefunc. See examples towards the end of my recent OSCON
presentation:
   http://www.joeconway.com/pres_oscon_2004-r1.pdf
and the source sql:
   http://www.joeconway.com/flex.sql

HTH,

Joe

Uwe C. Schroeder wrote:
> I wasn't able to get a usable answer by googling: Is there a way to create a
> view on a table that converts rows to columns ?
[snip]
> Is the above doable with a view?
> Any pointers will be appreciated.

Search the postgresql-sql list archives for "Arbitrary Cross-tab" for
recent discussion on this.

HTH
--
   Richard Huxton
   Archonet Ltd