469,106 Members | 2,304 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

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-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
2 1025
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Julia Briggs | last post: by
36 posts views Thread by rbt | last post: by
1 post views Thread by AAA | last post: by
25 posts views Thread by Piotr Nowak | last post: by
14 posts views Thread by bjorklund.emil | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.