Thank you all for the information. I ended up using Case. However, I have some instances where one ID has the same program but for different date spans.

Select P.ID,P.SSN,P.MCD_ID,P.Lname,P.Fname,P.Admit,P.Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID,

MAX(Case WHEN P.EligibleDesc = 'Program' Then P.Eligible END) As 'Eligible_PRG',

MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligibleType END) As 'EligibleType_PRG',

MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligibleDesc END) As 'Elig_PRG',

MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligBeginDate END) As 'EligBegin_PRG',

MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligEndDate END) As 'EligEnd_PRG',

From Eligibility_Final P

group by P.Patacctnum,P.SSN,P.MCD_ID_Instate,P.Lname,P.Fname,P.Admit,P.Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID

This produces semi-desired results; however, when some records have the same program but different date spans only the MAX dates are selected and the other records are eliminated. If I remove the MAX(Dates) then I get repeating rows. Can someone help me out with this issue?

The results I want would be:

ID,SSN,MCD_ID,Lname,Fname,Admit,Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID,Eligible_PRG,EligibleType_PRG,Elig_PRG,EligBegin_PRG,EligEnd_PRG,Eligible_PRG,EligibleType_PRG,Elig_PRG,EligBegin_PRG,EligEnd_PRG

Where the information in bold is the data for the first date span and the information in italics is the data for the second date span and so on.

Thanks!!