Solved

isnull function to combine records into 1 row

Posted on 2007-11-17
2
194 Views
Last Modified: 2010-03-19
Hi, I have a table that has the person, company and customer number. Thereare more than one rows for each customer. I want one row, with the corresponding company customer number. I performed a case statement, but am still getting back more than one row.
   Customer                    Company           CompanyID
1. Mouse, Mickey          CompanyA         CompanyA_ID
2. Mouse, Mickey           CompanyB        CompanyB_ID

I want Mouse, Mickey, CompanyA_ID  CompanyB_ID

SELECT Customer, CASE Company WHEN ComapnyA THEN CompanyA_ID END CompanyA_ID,
        CASE Company WHEN CompanyB THEN CompanyB_ID END CompanyB_ID
FROM CustomerTable

My result looks like this:
Mouse, Mickey      NULL      HI201
Mouse, Mickey      CB747      NULL

How can I group this to end up with one row?
I want Mouse, Mickey  CB747  H1201
0
Comment
Question by:Laura2112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
kselvia earned 300 total points
ID: 20306241
Try this

SELECT Customer, MAx ( CASE Company WHEN ComapnyA THEN CompanyA_ID END) CompanyA_ID,
        Max( CASE Company WHEN CompanyB THEN CompanyB_ID END) CompanyB_ID
FROM CustomerTable
Group by Customer
0
 

Author Comment

by:Laura2112
ID: 20306269
That works but I realize that if I have a common customer name, it isn't really what I need. I need some other identifier, like the SSN. Once I bring that in, the solution will work perfectly. Thank you!
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question