Howdy, Abhinandan,

A quick and dirty solution might be this :

SELECT *
FROM 
(
SELECT a.name,MAX(b.value) as height
FROM original a
LEFT JOIN original b
ON a.name = b.name
AND b.attribute = 'Height'
GROUP BY a.name
) height
NATURAL JOIN
(
SELECT a.name,MAX(b.value) as weigth
FROM original a
LEFT JOIN original b
ON a.name = b.name
AND b.attribute = 'Weight'
GROUP BY a.name
) weight
NATURAL JOIN 
(
SELECT a.name,MAX(b.value) as age
FROM original a
LEFT JOIN
  original
 b
ON a.name = b.name
AND b.attribute = 'Age'
GROUP BY a.name
) age

The thing is that it doesn't scale well if you have many more items beyond 
three...

Best,
Oliveiros
  ----- Original Message ----- 
  From: Abhinandan Raghavan 
  To: pgsql-sql@postgresql.org 
  Sent: Tuesday, December 06, 2011 1:57 PM
  Subject: [SQL] Self-Join


  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, 
               A.VALUE AS WEIGHT,
               B.VALUE AS HEIGHT,
               C.VALUE AS AGE


  FROM NAV A,
             NAV B,
             NAV C


  WHERE A.NAME = B.NAME
      AND A.NAME = 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

Reply via email to