need help with subquery in select

[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'));