you DID NOT post readily consumable sample data.
your sample headers had five columns, but the data only appears in three.
how is anyone supposed to be able to use that?
i ran what you posted thru my macro and came up with this:
;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])
AS
(
SELECT 'M2345','hf','1' UNION ALL
SELECT 'M2345','COPD','1' UNION ALL
SELECT 'M2345','CAD','1' UNION ALL
SELECT 'M2345','dia','1' UNION ALL
SELECT 'M2345','Ast','1' UNION ALL
SELECT 'M2345','hf','2' UNION ALL
SELECT 'M2345','COPD','2' UNION ALL
SELECT 'M2345','CAD','2' UNION ALL
SELECT 'M2345','dia','2' UNION ALL
SELECT 'M2345','Ast','2'
)
SELECT * FROM MySampleData
Lowell