SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SQL Query


Help with SQL Query

Author
Message
kshahborr99
kshahborr99
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
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
JohnnyDBA
JohnnyDBA
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1434 Visits: 897
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?
Luis Cazares
Luis Cazares
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39779 Visits: 19802
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 TABLE(
Name varchar(50),
Region varchar(50),
City varchar(50),
Status varchar(50),
N_Region varchar(50),
N_City varchar(50),
N_Status varchar(50),
E_Region varchar(50),
E_City varchar(50),
E_Stauts varchar(50));

INSERT @Test 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
GROUP BY Name
HAVING COUNT(*) = 1)
SELECT t.*
FROM @Test 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?


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
kshahborr99
kshahborr99
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
Thank you for your help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search