[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

isnull function to combine records into 1 row

Posted on 2007-11-17
2
Medium Priority
?
201 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 1200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

656 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