×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Pivot Table Help- need help finishing it up.

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
 

RE: Pivot Table Help- need help finishing it up.

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

RE: Pivot Table Help- need help finishing it up.

(OP)
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.
 

RE: Pivot Table Help- need help finishing it up.

(OP)
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.

  

RE: Pivot Table Help- need help finishing it up.

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

RE: Pivot Table Help- need help finishing it up.

(OP)
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.

RE: Pivot Table Help- need help finishing it up.

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close