Hi Abhinandan, I suppose you mean this:
CREATE TABLE nav ( name varchar NOT NULL, attribute text NOT NULL, value numeric ); ALTER TABLE ONLY nav ADD CONSTRAINT nav_pkey PRIMARY KEY (name, attribute); insert into nav values ('James','Weight',70); insert into nav values ('James','Height',165); insert into nav values ('James','Age',22); insert into nav values ('David','Weight',75); insert into nav values ('David','Height',180); So we are at least sure we do not have duplicates. Now, SELECT DISTINCT n.name as Name FROM nav as n ORDER BY 1 DESC; will give us the base name list If you could trust all values to be there, you'd do something like: SELECT n1.name as name, n2.weight as weight, n3.age as age FROM (SELECT DISTINCT name as Name FROM nav as n ORDER BY 1 DESC) as n1, (SELECT name as Name, value as weight FROM nav as n WHERE attribute='Weight' ) as n2, (SELECT name as Name, value as age FROM nav as n WHERE attribute='Age' ) as n3 WHERE n1.name = n2.name AND n1.name = n3.name; Since "Age" may be missing, you need to make an OUTER join for it: SELECT n1.name as name, n2.height as height, n3.weight as weight, n4.age as age FROM (SELECT DISTINCT name as Name FROM nav as n ORDER BY 1 DESC) as n1, (SELECT name as Name, value as height FROM nav as n WHERE attribute='Height' ) as n2, (SELECT name as Name, value as weight FROM nav as n WHERE attribute='Weight' ) as n3 LEFT OUTER JOIN (SELECT name as Name, value as age FROM nav as n WHERE attribute='Age' ) as n4 ON n3.name = n4.name WHERE n1.name = n2.name AND n1.name = n3.name; I find this data design terrible, but I'm sure you have no more love for it then I do :) It looks like you just inherited from someone else :) Bèrto On 6 December 2011 16:57, Abhinandan Raghavan <abhinandan.ragha...@unige.ch>wrote: > Hi, > > I'm looking to frame an SQL statement in Postgres for what's explained in > the attached image. > > The original table is at the top and is called NAV (Short for Name, > Attribute, Value). I want to create a view (NWHA_View) involving values > from within (presumably from a self join). I would've normally created a > view in the following way: > > > SELECT A.NAME <http://a.name/>, > A.VALUE AS WEIGHT, > B.VALUE AS HEIGHT, > C.VALUE AS AGE > > FROM NAV A, > NAV B, > NAV C > > WHERE A.NAME <http://a.name/> = B.NAME <http://b.name/> > AND A.NAME <http://a.name/> = C.NAME <http://c.name/> > AND A.ATTRIBUTE = 'Weight' > AND B.ATTRIBUTE = 'Height' > AND C.ATTRIBUTE = 'Age' > > > The only problem when I create a view with the above select statement is > that when there are no entries for the field name "AGE" (in the case of > David), then the row does not get displayed. What's the way out in > Postgresql? I know the way it is addressed in Oracle but it doesn't seem to > work in Postgresql. > > Thanks. > > Abhi > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- ============================== If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.