CODEWITHSUNDEEP
sqlsql-servert-sql
Chris
Chris

Reputation: 1766

SQL Combine null rows with non null

Due to the way a particular table is written I need to do something a little strange in SQL and I can't find a 'simple' way to do this

Table

Name   Place     Amount
Chris  Scotland  
Chris            £1  
Amy    England
Amy              £5

Output

Chris  Scotland  £1
Amy    England   £5

What I am trying to do is above, so the null rows are essentially ignored and 'grouped' up based on the Name

I have this working using For XML however it is incredibly slow, is there a smarter way to do this?

Upvotes: 2

Views: 62

Answers (1)

S3S
S3S

Reputation: 25112

This is where MAX would work

select 
   Name
   ,Place = Max(Place)
   ,Amount = Max(Amount)
from
   YourTable
group by
   Name

Naturally, if you have more than one occurance of a place for a given name, you may get unexpected results.

Upvotes: 4

Related Questions