September 8, 2012 at 7:31 pm
Hi,
I have a table which looks like :
Name Region City Status N_Region N_City N_Status E_Region E_City E_Stauts
Peter AMERICA NY A EMEA LDN U AMERICAS NY A
SHAWN AMERICA NY A EMEA LDN U AMERICAS LA A
SHAWN AMERICA NY A EMEA LDN U AMERICAS NY A
TONY AMERICA NY A EMEA LDN U AMERICAS LA A
TONY AMERICA NY A EMEA LDN U EMEA LDN U
I am looking for a query which will give me a line as it is if the Name is UNIQUE ( In above eg : Peter )
If the name is duplicated than to give where the
Region and City are unique to N_Region and N_City and to E_Region and E_City
So the result should be :
Name Region City Status N_Region N_City N_Status E_Region E_City E_Stauts
Peter AMERICA NY A EMEA LDN U AMERICAS NY A
SHAWN AMERICA NY A EMEA LDN U AMERICAS LA A
TONY AMERICA NY A EMEA LDN U AMERICAS LA A
Any help would be much appreciated
Thanks
September 10, 2012 at 12:15 pm
How are you determining which record is the "correct" record to return for the duplicates? For instance, how do you know you want the LA Shawn and not the NY Shawn? Are you just taking a random order?
September 10, 2012 at 12:42 pm
Welcome to SQLServerCentral!
In order to help you, you need to help us. You should post your DDL and sample data in a consumable format (a way on which we can just run the script). As it's your first time, I'll do it for you:
DECLARE @test-2TABLE(
Namevarchar(50),
Region varchar(50),
City varchar(50),
Statusvarchar(50),
N_Region varchar(50),
N_City varchar(50),
N_Status varchar(50),
E_Region varchar(50),
E_City varchar(50),
E_Stautsvarchar(50));
INSERT @test-2 VALUES('Peter', 'AMERICA', 'NY', 'A ', 'EMEA ', 'LDN ', 'U ', 'AMERICAS ', 'NY ', 'A'),
('SHAWN', 'AMERICA', 'NY', 'A ', 'EMEA ', 'LDN ', 'U ', 'AMERICAS ', 'LA ', 'A'),
('SHAWN', 'AMERICA', 'NY ', 'A ', 'EMEA ', 'LDN ', 'U ', 'AMERICAS ', 'NY ', 'A'),
('TONY', 'AMERICA', 'NY ', 'A ', 'EMEA ', 'LDN ', 'U ', 'AMERICAS ', 'LA ', 'A'),
('TONY', 'AMERICA', 'NY ', 'A ', 'EMEA ', 'LDN ', 'U ', 'EMEA ', 'LDN ', 'U');
However, your description is not clear enough. If it's just a duplicate problem, you SHOULD look for other answers (I did that for you too)
http://www.sqlservercentral.com/search/?q=duplicate+records
I am looking for a query which will give me a line as it is if the Name is UNIQUE
That could be accomplished with this:
WITH UniqueNames AS(
SELECT Name
FROM @test-2
GROUP BY Name
HAVING COUNT(*) = 1)
SELECT t.*
FROM @test-2 t
JOIN UniqueNames u ON t.Name = u.Name;
The next part if the problem is the part I can't understand
If the name is duplicated than to give where the
Region and City are unique to N_Region and N_City and to E_Region and E_City
Can you explain more?
October 14, 2012 at 12:41 pm
Thank you for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply