How to list all pairs of location with same area from the table in Oracle?

Question

I have been trying to find the answer to this question.

List all pairs of locations that have same area. The result should list (location name 1, location name 2, location area) where location name 1 and location name 2 have same location area. This pair should appear only once in the output.

So the table can look like this:

(Loc_id, Loc_name, Loc_area)
(1, ABC, 60)
(2, ZXY, 50)
(3, DEF, 60)
(4, YUM, 60)
(5, ZUM, 50)

Pairs: (ABC,DEF,60), (ZXY,ZUM,50), (ABC,YUM,60), (DEF,ZUM,60), and so on.

UPDATE:

I get a table with first name, second name and location area with Pratik's solution. But, it doesn't give any value in this table.

what if I do this?

select t_1.Loc_name name1, t_2.loc_name name2, t_1.loc_area
from Location t_1, Location t_2
where t_1.loc_area = t_2.loc_area and t_1.loc_name<>t_2.loc_name
Order by t_1.Loc_name

I get the list of all probable combinations (similar to Rebika's solution below). But now how do i remove duplicates from this list?

I don't want

name1      name2     loc_area
ABC         DEF        60
DEF         ABC        60

I want

name1      name2     loc_area
ABC        DEF         60
ABC        YUM         60
DEF        YUM         60
.
.
.

Thanks.

See http://stackoverflow.com/questions/4071811/how-to-transform-vertical-data-into-horizontal-data-with-sql — Gilbert, May 19 '13 at 22:38

score 1 · Accepted Answer · answered May 21 '13 at 04:11

1

Try this,

SELECT a.loc_name, b.loc_name, a.loc_area
  FROM LOCATION a, LOCATION b
 WHERE a.loc_area = b.loc_area
   AND a.loc_name != b.loc_name
   AND a.loc_id < b.loc_id;

First condition ensures that records with same loc_area are joined.
Second condition ensures loc_name is not joined with itself.
Third condition ensures only one combination of loc_name is returned.

answered May 21 '13 at 04:11

Noel

10,152
30
45
67

this is a perfect solution! Guess I needed to add t_1.loc_id < t_2.loc_id; – comwiz756 May 21 '13 at 21:23

score 0 · Answer 2 · answered May 19 '13 at 22:44

Since you didn't specify your oracle version, you can use this if you are using Oracle 11g:

select loc_area, listagg (loc_name, ',') WITHIN GROUP 
(ORDER BY loc_name) Locations
FROM LOCATION
GROUP BY LOC_AREA;

If you are on Oracle 9, try using xmlagg:

select loc_area, rtrim (xmlagg (xmlelement (e, loc_name || ',')).extract ('//text()'), ',') Locations
from LOCATION
group by LOC_AREA ;

source: http://www.dba-oracle.com/t_converting_rows_columns.htm

score 0 · Answer 3 · answered May 19 '13 at 22:51

0

I think following query will solve your question -

select t_1.Loc_name name1, t_2.loc_name name2, t_1.loc_area
from Location t_1, Location t_2
where t_1.loc_area = t_2.loc_area
and t_1.loc_area<t_2.loc_area
Order by t_1.Loc_name

answered May 19 '13 at 22:51

pratik garg

3,282
1
17
21

this doesn't return any value – comwiz756 May 20 '13 at 01:46
what if I add t_1.loc_name<>t_2.loc_name? – comwiz756 May 20 '13 at 02:09

score 0 · Answer 4 · answered May 20 '13 at 08:38

0

Try this out -:

SELECT a.Loc_name locname1, b.Loc_name locname2, a.Loc_area
FROM Location a
JOIN Location b
ON 1=1
WHERE a.area =b.area and a.Loc_name <>b.Loc_name

I have avoided those location names that pairs up with the self such as (ABC,ABC). If you want this too remove condition a.loc_name <>b.loc_name in where clause.

I hope this is what you are looking for.

answered May 20 '13 at 08:38

Rebika

179
5

This is similar to the one that I updated above. But it still has (abc, def) and (def, abc) pairs which are similar. It should only return either (abc, def) pair or (def, abc) pair. – comwiz756 May 20 '13 at 21:26
Add a.Loc_id < b.Loc_id or b.Loc_id >a.Loc_id in the where clause in above query and you will get the desired result.Noel 's solution is also correct. – Rebika May 21 '13 at 05:31

How to list all pairs of location with same area from the table in Oracle?

4 Answers4

Linked