SQL Combine null rows with non null

Question

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?

S3S · Accepted Answer · 2018-09-07 16:24:11Z

4

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.

answered Sep 7, 2018 at 16:24

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S3S Over a year ago

Ah no worries at all @Chris

Chris Over a year ago

when doing max against a string is it using the char length to determine the 'max'?

S3S Over a year ago

No, it's using the ASCII conversion ordering though length can come into play here. For example, c comes after b so it would be returned for the max. however, bb would be returned over c. case sensitivity only matters if your collation is case sensitive. This also applies for order by on a varchar field. You can make a table of random strings and order by it to see how it'd be sorted (and this would also determine he min and max by the first and last row sorted asc)

Collectives™ on Stack Overflow

SQL Combine null rows with non null

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related