Consolidating records

Question

Consolidating records

FishD

Right there with Babe

Points: 784
More actions
January 31, 2014 at 1:47 pm

#281816

Hi all,
I have the following table
fname,lname,location1,location2
-------------------------------
john, doe, dublin, null
john, doe, null, galway
and i wish to consolidate it to
fname,lname,location1,location2
-------------------------------
john, doe, dublin, galway.
I was able to do it by creating a new table, and rejoining, but it is cumbersome,
Is there an easier way
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Not 100% certain what you are looking for as you didn't post a lot of details. It would be very helpful if you could post ddl and sample data in the future too.

This works with the information you posted.

create table #Something

(

fname varchar(10),

lname varchar(10),

location1 varchar(10),

location2 varchar(10)

)

insert #Something

select 'john', 'doe', 'dublin', null union all

select 'john', 'doe', null, 'galway'

select MAX(fname), MAX(lname), MAX(location1), MAX(location2)

from #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

It's more like:

SELECT fname, lname, MAX(location1), MAX(location2)

FROM #Something

GROUP BY fname, lname

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Luis Cazares (1/31/2014)
It's more like:
SELECT fname, lname, MAX(location1), MAX(location2)
FROM #Something
GROUP BY fname, lname

Probably true. I didn't put much into since it is late on Friday afternoon. 😛