Solved

isnull function to combine records into 1 row

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

730 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