Pivot Table Help- need help finishing it up.

[ETOMBS]

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

 

[r937]

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
     , MAX(CASE L1.lead_interest.searchtype_id 
                WHEN '20061112175927'
                THEN 'Yes' ELSE NULL END) as Mountains
     , MAX(CASE L1.lead_interest.searchtype_id 
                WHEN '20061112175933'
                THEN 'Yes' ELSE NULL END) as Coast
     , MAX(CASE L1.lead_interest.searchtype_id 
                WHEN '20061112175939'
                THEN 'Yes' ELSE NULL END) as Lake
     , MAX(CASE L1.lead_interest.searchtype_id 
                WHEN '20061112175943'
                THEN 'Yes' ELSE NULL END) as River 
  FROM shows.show_lead
INNER 
  JOIN L1.lead_info    
    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 shows.show_lead.showid = 67
GROUP
    BY 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

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ETOMBS]

I tried the group by option but that doesn't work. I end up with one record for each lead_id but lose all but one piece of geographical data.

 

[PHV]

but that doesn't work
any chance you could elaborate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ETOMBS]

if the starting Data looks like this:

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

Adding the 'group by' clause makes it look like this:

Lead_info Mountains Coast Lake River
1 yes
2 yes
3 yes


I lose data that I'm trying to merge into one row.

 

[r937]

where do the 1 and 3 come from? what columns are those?

your data display is confusing



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

Adding the 'group by' clause
with the MAX aggregate functions ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ETOMBS]

The Lead_id is directly linked to a specific person.(The primary key on another table) They can elect what geographical interests they have when looking for a new home. For each choice they make, a new record is added to the lead_Interest table.

The interest are located in one column, each with its onwn unique value. I have to split these values out into their ouwn columns to display thier interests across a single row with thier lead_id at the beginning. If they have multiple interests, they should all be present and accounted for.

 

[ETOMBS]

nervmind, the max aggregate worked, where it wasn't working before. I missed one MAX in the setting and it screwed it all up. Thanks again for all the help!