March 6, 2018 at 9:47 am
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.
TABLE1
PATIENT ID
PatientA 1234
PatientB 5678
TABLE2
ID CODE VALUE
1234 Code1 Value1
1234 Code2 Value2
1234 Code3 Value3
5678 Code1 Value1
5678 Code2 Value2
5678 Code3 Value3
NEEDED FORMAT - OUTPUT
PATIENT ID Code1 Code2 Code3
PatientA 1234 Value1 Value2 Value3
PatientB 5678 Value1 Value2 Value3
March 6, 2018 at 1:55 pm
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;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply