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:
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,
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.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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)
FROM (SELECT ptid, vital_name, vital_result
PIVOT (MAX( vital_result ) FOR vital_name IN ('Height', 'Weight'));
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb