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?