Solved

isnull function to combine records into 1 row

Posted on 2007-11-17
2
188 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
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

839 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