Skip site navigation (1) Skip section navigation (2)

Is it possible...

From: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is it possible...
Date: 2004-08-24 15:47:43
Message-ID: 200408240847.43584.uwe@oss4u.com (view raw or flat)
Thread:
Lists: pgsql-general
-----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-----


Responses

pgsql-general by date

Next:From: Gaetano MendolaDate: 2004-08-24 15:54:25
Subject: Re: No connection to a PG 8.0 Beta 1 win32 server
Previous:From: mikeDate: 2004-08-24 15:40:25
Subject: Re: Invalid input for integer on VIEW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group