Pivot Table Help- need help finishing it up.
Pivot Table Help- need help finishing it up.
(OP)
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
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
RE: Pivot Table Help- need help finishing it up.
CODE
, 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
RE: Pivot Table Help- need help finishing it up.
RE: Pivot Table Help- need help finishing it up.
any chance you could elaborate ?
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Pivot Table Help- need help finishing it up.
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.
RE: Pivot Table Help- need help finishing it up.
your data display is confusing
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Pivot Table Help- need help finishing it up.
with the MAX aggregate functions ?
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Pivot Table Help- need help finishing it up.
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.
RE: Pivot Table Help- need help finishing it up.