Merge records in the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merge records in the same table

Author

Topic

skysurfer
Starting Member

2 Posts

Posted - 2015-02-04 : 08:46:24

Hello.

I need to normalize a table that contains anagraphic data.
Here is a sample table with some rows:

Id LastName FirsName Address Email Tel Mobile Age
1 Rossi Mario Via Milano NULL 123456 NULL 41
2 Rossi Mario NULL rm@test.it 123456 NULL NULL
3 Rossi Mario Via Milano NULL NULL 254521 NULL

I want merge the values in the records in order to have one unique record with all values.
Like this:

Id LastName FirsName Address Email Tel Mobile Age
1 Rossi Mario Via Milano rm@test.it 123456 254521 41

I tried to use the MERGE statement, but I don't think this is the right solution.

Any help is appreciated.

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-04 : 09:16:30

Try:

With rankresult
As
(
Select
Id, Row_Number() Over(Partition By LastName Order BY LastName Asc) rn,
LastName FirsName, Address, Email, Tel, Mobile, Age
From mytable
)
Select * From rankresult
Where Not Null In ('LastName,'FirstName', 'Address', 'Email', 'Tel', 'Mobile', 'Age')
And rn = 1

We are the creators of our own reality!

skysurfer
Starting Member

2 Posts

Posted - 2015-02-05 : 04:14:12

Hello sz1. Thank you for your answer.
Probably I have not explained properly my problem (my english is so bad!)

I don't want to get the first row of a group of record, but I want to have a single record that contains all the NOT NULL values from the other record with same First Name and Last Name.

Thank you.

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-05 : 08:12:30

supposed that your "not null" values are unique to each person...

SELECT
MIN(id) id, -- No idea what do you have and what you need on that column for each person
LastName,
FirstName,
MIN(address) address,
MIN(Email) Email,
MIN(Tel) Tel,
MIN(Mobile) Mobile,
MAX(Age) Age
FROM TABLE_A
GROUP BY LastName, FirstName

------------------------
PS - Sorry my bad english

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources