## Help with Pivoting Data

 Author Message aobf SSC-Enthusiastic Group: General Forum Members Points: 125 Visits: 41 Folks,I'm looking for some suggestions. In my job, I work with value mapping on a daily basis, many times I am confronted with tables like below. When there is only 1 value mapping I needed to collapse, I was able to successfully use the Pivot function but most of the time, I will have multiple value mapping (1 row for each) that need to be "pivoted" so that I have 1 row per patient. I can't use pivot because its looking to aggregate the value. I need to be able to collapse the rows into 1 row. Please let me know if you can help me out. Let me know if you need more information.TABLE1PATIENT ID PatientA 1234PatientB 5678TABLE2 ID CODE VALUE1234 Code1 Value11234 Code2 Value21234 Code3 Value3 5678 Code1 Value15678 Code2 Value25678 Code3 Value3 NEEDED FORMAT - OUTPUTPATIENT ID Code1 Code2 Code3PatientA 1234 Value1 Value2 Value3PatientB 5678 Value1 Value2 Value3 sgmunson SSC Guru Group: General Forum Members Points: 98349 Visits: 7263 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 Code3FROM TABLE1 AS P INNER JOIN TABLE2 AS C ON P.ID = C.IDGROUP BY P.PATIENT, P.IDORDER BY P.ID;` Steve‌(aka sgmunson)‌ ‌Health & Nutrition