I'm trying to develop a pivot table where the values from one row are put into separate columns on my output. My problem is not separating the vaules into columns, but rather, re-assmbling them into one record with a distinct key, because each row can have more than one selected value for the pivot table. I also think I'm gettign confused because I have to join several tables together already to just collect the information I need so any help would be greatly appreciated.

My data looks currently looks like this by the end of the query I currently have:

Lead_info        Mountains    Coast    Lake    River
1                    yes          
1                                  yes
1                                           yes
1                                                   yes
2                    yes
3                    yes                   
3                                           yes


And is should look like this:

Lead_info        Mountains    Coast    Lake    River
1                    yes          yes      yes     yes
2                    yes
3                    yes                   yes        
   
This is what I have so far and it gets me my first set of results. What do I do next?

select l1.lead_info.lead_id, L1.lead_info.fname,
L1.lead_info.lname,L1.lead_info.address,L1.lead_info.city,
L1.lead_info.state,L1.lead_info.phone,L1.lead_info.email,
 (case L1.lead_interest.searchtype_id when '20061112175927'then 'Yes' END) as Mountains,
(case L1.lead_interest.searchtype_id when '20061112175933'then 'Yes' END) as Coast,
(case L1.lead_interest.searchtype_id when '20061112175939'then 'Yes' END) as Lake,
(case L1.lead_interest.searchtype_id when '20061112175943'then 'Yes' END) as River
 from lead_info
  left outer join shows.show_lead
  on L1.lead_info.lead_id=shows.show_lead.leadid
  left outer join L1.lead_interest
  on L1.lead_interest.lead_id=L1.lead_info.lead_id
   where L1.lead_info.lead_id=shows.show_lead.leadid
   and shows.show_lead.showid=67
   and L1.lead_interest.lead_id=L1.lead_info.lead_id