SQLTeam.com | Weblogs | Forums

Source destination without duplication

Hi everybody, I'm trying to create a new data table using the example below (the table contains tousands of entry so this is just part of it):
City Language code
Paris French 2
New York English 3
Delhi English 3
Berlin German 5
Marseille French 2
Hamburg German 5
Munich German 6

the ouput should be something like this:

Source Destination Language code
Paris Marseille French 2
NY Delhi English 3
Berlin Hamburg German 5

The purpose is to have a source X, destination Y based on same language and same code without having source Y, destination X in the table.
Please advise on how to achieve it.

Hi Harish, thank you so much for your reply. Is your assumption important in the sql statement, because i have actually many rows with same language and same code, and thus output should be multiple source X destination Y without source Y destination X

Hi Harish,
The input: for same Code C and Language L we have cities X, Y , Z for example, then cities output should be X Y / X Z / Z Y. The order source, destination doesn t matter (X Y or Y X)

hi

is it dynamic
.. means for 1 code and language you could have 3 records
..............for another code and language you could have 5 records

is this how it would be !!

image

Hi, thanks for ur feedback :slight_smile: . Yes it s dynamic you can have different scenarios and the ouput is what you stated also.

hi

i have the SQL ..

here .. there is a case where there is only 1 row ..
my output is excluding that .. do we want that also ???
for code 6

SELECT DISTINCT b.city, 
                a.city, 
                b.code, 
                b.language 
FROM   data b, 
       data a 
WHERE  a.city <> b.city 
       AND b.city < a.city 
       AND a.code = b.code 
       AND a.language = b.language 
ORDER  BY code, 
          language 

image

image

Hi Harish, amazing!! No for one row it should be excluded like u said. Thank u so much!!

Njoy !!!!

:slight_smile:

:+1: