need help with subquery in select

[dmcmillan]

I want to pull from a table both the weight and height, they are in same table with a name field of either weight or height and a value field.
I'm trying this, but the subquery returns more than one value:
select b.pat_ext_id,
a.vital_result as height,
(select d.vital_result from MRVL99 d where D.PTID = b.ptid and d.vital_name = 'Weight') as weight
from MRVL99 a,
MRPA99 b
where
a.PTID = B.PTID
and a.vital_name = 'Height'
order by b.pat_ext_id

Any ideas are greatly appreciated. I'm also afraid that this only gives me results if a person has both a height and a weight in the table.

[PAVB]

Please post table desc

Either way - if table design is any close to what I think it is, a self join will do it, no need for a subquery.

[dmcmillan]

Table MRVL99
Column DataType
PTID Number(11)
D_VITAL Date
VITAL_NAME Char(30 Byte)
VITAL_RESULT Char(20 Byte)

[LKBrwn_DBA]

Try something like this:

WITH mrvl99 AS
        (SELECT 1 ptid, SYSDATE d_vital,'Weight' vital_name, 101.1 vital_result FROM DUAL UNION
         SELECT 1, SYSDATE,'Height',   5.2 FROM DUAL UNION
         SELECT 2, SYSDATE,'Weight', 212.2 FROM DUAL UNION
         SELECT 2, SYSDATE,'Height',   5.7 FROM DUAL UNION
         SELECT 3, SYSDATE,'Weight', 183.3 FROM DUAL UNION
         SELECT 3, SYSDATE,'Height',  4.11 FROM DUAL)
SELECT *
  FROM (SELECT ptid, vital_name, vital_result
          FROM mrvl99) 
 PIVOT (MAX( vital_result ) FOR vital_name IN ('Height', 'Weight'));