Hi,
I am looking for a way to transpose data.
I would imagine the syntax would happen in the SELECT statement somewhere, prior to creating a table?
Using this simple query below as an example I would like this normal output
ID Code Description Count of Code
30884766 S9470 Nutritional counseling, dietitian visit 18
30884766 S9460 Diabetic management program, nurse visit 12
30884766 H2035 Alcohol and/or other drug treatment program 6
To look like this with one row by ID and each of the other columns to follow:
ID Code Description1 Count of Code 1 Code2 Description2 Count of Code 2 Code3 Description3 Count of Code 3
30884766
Here is the basic script to create the normal output:
SELECT a.ID as ID
,a.Code as Code
,w.Description as Description
,COUNT (a.Code) as “Count of Code”
INTO RiskAdjust_Temp.dbo.WHP_CPP_PROF
FROM RiskAdjust_BHI.dbo.PROFOTHER a
INNER JOIN RiskAdjust_Temp.dbo.WHP_CPT_HCPCS w
on a.Code = w.code
INNER JOIN RiskAdjust_BHI.dbo.MEMBER_QUALIFIED_CLAIMS_PROFOTHER d
on a.Id = d.ID
WHERE a.Code in (‘H0002’,‘H0012’,‘H0013’,‘H2001’,‘H2035’,‘H2036’,‘S0315’,‘S0316’,‘S0317’,‘S0320’,‘S0340’,‘S0341’,
‘S0342’,‘S9140’,‘S9141’,‘S9455’,‘S9460’,‘S9465’,‘S9470’,‘S9472’,‘S9473’,‘S9476’,‘T1023’,‘4242F’,
‘4500F’,‘H0018’,‘H0028’,‘H0037’)
and d.RISK_YEAR = 2012
GROUP BY a.ID, a.Code, w.Description;
Thank you for any advice
WHP
