Could you please help me to solve this Below Query:

I have below table of data.

I want output as like below:

PLSQL we can write block to get this output. But is it possible to get output using SQL code alone?

CodePudding user response：

Looks like a self join.

SQL> with temp (empno, name, city) as
  2    (select 1, 'John'   , 'US'  from dual union all
  3     select 2, 'Miranda', 'US'  from dual union all
  4     select 3, 'Pete'   , 'US'  from dual union all
  5     select 4, 'Jack'   , 'US'  from dual union all
  6     select 5, 'Kathy'  , 'UK'  from dual union all
  7     select 6, 'Tanni'  , 'UK'  from dual union all
  8     select 7, 'Sally'  , 'UAE' from dual
  9    )
 10  select a.city, a.name, b.name
 11  from temp a join temp b on a.city = b.city and a.name < b.name
 12  order by a.city, a.name;

CIT NAME    NAME
--- ------- -------
UK  Kathy   Tanni
US  Jack    Miranda
US  Jack    John
US  Jack    Pete
US  John    Pete
US  John    Miranda
US  Miranda Pete

7 rows selected.

SQL>

CodePudding user response：

with
  input_table (empno, name, city) as (
    select 1, 'John'   , 'US'  from dual union all
    select 2, 'Miranda', 'US'  from dual union all
    select 3, 'Pete'   , 'US'  from dual union all
    select 4, 'Jack'   , 'US'  from dual union all
    select 5, 'Kathy'  , 'UK'  from dual union all
    select 6, 'Tanni'  , 'UK'  from dual union all
    select 7, 'Sally'  , 'UAE' from dual
  )
-- end of sample data (for testing only, not part of the query)
-- remove WITH clause and use your actual table name below
select t1.city, t1.name as name1, t2.name as name2
from   input_table t1 inner join input_table t2
                      on t1.city = t2.city and t1.empno < t2.empno
order  by t1.empno, t2.empno   -- if needed
;

CITY  NAME1    NAME2  
----- -------- --------
US    John     Miranda
US    John     Pete   
US    John     Jack   
US    Miranda  Pete   
US    Miranda  Jack   
US    Pete     Jack   
UK    Kathy    Tanni