Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pivot Table Help- need help finishing it up.

Status
Not open for further replies.
ETOMBS

ETOMBS

Programmer
Aug 24, 2010
7
US
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
 
Sort by date Sort by votes
Code: 
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
but that doesn't work
any chance you could elaborate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
Adding the 'group by' clause
with the MAX aggregate functions ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
364
mmiah1982
mmiah1982
Bill Doe
  • Locked
  • Question
PDO UPDATE too long to execute
Replies
1
Views
341
mikrom
mikrom
szeiss
  • Locked
  • Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
405
szeiss
szeiss
harfri
  • Locked
  • Question
Insert into a new table
Replies
0
Views
289
harfri
harfri
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
379
carp
carp

Part and Inventory Search

Sponsor

Back
Top