question

SELECT sap,[Enrollment Effective] as EnrollmentEffective, [Enrollment Signature] As EnrollmentSignature FROM (select SAP, eventtype, expr1002 from test) as a pivot ( min(expr1002) for eventtype in ([Enrollment Effective], [Enrollment signature])) as p

There is a simple way of doing this. In this example I've embedded your test data in the solution for convenience Select sap, sum(case when eventtype='Enrollment Effective' then Expr1002 else 0 end) as [EnrollmentEffective], sum(case when eventtype='Enrollment Signature' then Expr1002 else 0 end) as [EnrollmentSignature] from (values (0, 'Enrollment Effective', 1), (0, 'Enrollment Signature', 1), (1, 'Enrollment Effective', 2), (1, 'Enrollment Signature', 2)) data(sap,eventtype,Expr1002) group by sap Or another alternate way of demonstrating this is ... declare @data table (sap int, EventType varchar(80), Expr1002 int) insert into @data (sap,eventtype,Expr1002) values (0, 'Enrollment Effective', 1), (0, 'Enrollment Signature', 1), (1, 'Enrollment Effective', 2), (1, 'Enrollment Signature', 2) Select sap, sum(case when eventtype='Enrollment Effective' then Expr1002 else 0 end) as [EnrollmentEffective], sum(case when eventtype='Enrollment Signature' then Expr1002 else 0 end) as [EnrollmentSignature] from @data d group by sap