Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with SQL Query Expand / Collapse
Author
Message
Posted Saturday, September 08, 2012 7:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 14, 2012 12:40 PM
Points: 2, 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
Post #1356433
Posted Monday, September 10, 2012 12:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 9:05 AM
Points: 336, Visits: 766
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?
Post #1356943
Posted Monday, September 10, 2012 12:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 2,785, Visits: 5,967
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356958
Posted Sunday, October 14, 2012 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 14, 2012 12:40 PM
Points: 2, Visits: 3
Thank you for your help
Post #1372526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse