?
Solved

isnull function to combine records into 1 row

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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