dimanche 19 avril 2015

SQL grouping interescting/overlapping rows

I have the following table in Postgresql that has overlapping data in the two columns a_sno and b_sno.



drop table data;
create table data
( a_sno integer not null,
b_sno integer not null,
CONSTRAINT ab PRIMARY KEY (a_sno,b_sno)
);

insert into data (a_sno,b_sno)
values (4,5),(5,4),(5,6),(6,5),(6,7),(7,6),
(9,10),(10,9),(9,13),(13,9),(10,13),(13,10),(10,14),(14,10),(13,14),(14,13),
(11,15),(15,11);
a_sno b_sno
4 5
5 4
5 6
6 5
6 7
7 6
9 10
9 13
10 9
10 13
10 14
11 15
13 9
13 10
13 14
14 10
14 13
15 11


As you can see from the first 6 rows data values 4,5,6 and 7 in the two columns intersects/overlaps that need to partitioned to a group. Same goes for rows 7-16 and rows 17-18 which will be labeled as group 2 and 3 respectively.


The resulting output should look like this. Thanks in advance for your help.



group|value
1|4
1|5
1|6
1|7
2|9
2|10
2|13
2|14
3|11
3|15

Aucun commentaire:

Enregistrer un commentaire