Try this "CROSS TAB" query:WITH TABLE1 (PATIENT, ID) AS (

    SELECT 'PatientA', 1234 UNION ALL
    SELECT 'PatientB', 5678
),
    TABLE2 (ID, CODE, [VALUE]) AS (

        SELECT 1234, 'Code1', 'Value1' UNION ALL
        SELECT 1234, 'Code2', 'Value2' UNION ALL
        SELECT 1234, 'Code3', 'Value3' UNION ALL
        SELECT 5678, 'Code1', 'Value1' UNION ALL
        SELECT 5678, 'Code2', 'Value2' UNION ALL
        SELECT 5678, 'Code3', 'Value3'
)
SELECT P.PATIENT, P.ID,
    MAX(CASE C.CODE WHEN 'Code1' THEN [VALUE] ELSE NULL END) AS Code1,
    MAX(CASE C.CODE WHEN 'Code2' THEN [VALUE] ELSE NULL END) AS Code2,
    MAX(CASE C.CODE WHEN 'Code3' THEN [VALUE] ELSE NULL END) AS Code3
FROM TABLE1 AS P
    INNER JOIN TABLE2 AS C
        ON P.ID = C.ID
GROUP BY P.PATIENT, P.ID
ORDER BY P.ID;