Home » SQL & PL/SQL » SQL & PL/SQL » Oracle pair combination with two column values in a table (Oracle 11g)
| Oracle pair combination with two column values in a table [message #642001] |
Sat, 29 August 2015 11:45  |
 |
sunshine
Messages: 7
Registered: August 2015
|
Junior Member |
|
|
I have oracle table with 2 columns
column1 column2
A B
B C
D E
C F
need output to have groups for chained member combination pairs, group number is assigned based on the combination of pairs , as D and E are not repeated so they are assigned to one group,
A comes with B, B comes with C and C comes with F, all of them have some kind of relation so they should assign to one unique group number , desired output is given below
Column Group
A 1
B 1
C 1
F 1
D 2
E 2
please let me know sql or plsql
|
|
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642003 is a reply to message #642002] |
Sat, 29 August 2015 12:09  |
|
sunshine
Messages: 7
Registered: August 2015
|
Junior Member |
|
|
(updated with formatting)
I have oracle table with 2 columns with values below
column1 column2
A B
B C
D E
C F
need output to have groups for chained member combination pairs, group number is assigned based on the combination of pairs , as D and E are not repeated so they are assigned to one group,
A comes with B, B comes with C and C comes with F, all of them have some kind of relation(occurances) so they should assign to one unique group number , desired output is given below
Column Group
A 1
B 1
C 1
F 1
D 2
E 2
please let me know sql or plsql
[Updated on: Sat, 29 August 2015 12:11] Report message to a moderator |
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642004 is a reply to message #642003] |
Sat, 29 August 2015 12:46 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Recursive SQL solution:
with r(
col,
grp
) as (
select column1 col,
row_number() over(order by column1) grp
from tbl
where column1 not in (select column2 from tbl)
union all
select t.column2,
r.grp
from r,
tbl t
where t.column1 = r.col
)
select col,
grp
from r
order by grp,
col
/
C GRP
- ----------
A 1
B 1
C 1
F 1
D 2
E 2
6 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642010 is a reply to message #642009] |
Sat, 29 August 2015 14:48 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Or:
with data as (
select connect_by_root column1 root,
column2
from tbl
connect by prior column2 = column1
start with column1 not in (select column2 from tbl)
)
select nvl(column2,root) col,
dense_rank() over(order by root) grp
from data
group by grouping sets((root),(root,column2))
order by grp,
col
/
C GRP
- ----------
A 1
B 1
C 1
F 1
D 2
E 2
6 rows selected.
SQL>
SY.
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642013 is a reply to message #642001] |
Sat, 29 August 2015 23:13 |
|
sunshine
Messages: 7
Registered: August 2015
|
Junior Member |
|
|
Thanks SY and MC, I really appreciate your prompt responses
let me explain little more detail on the scenarios below
Create table t(col1 varchar2(1),col2 varchar2(1));
insert into t values('A','B');
insert into t values('B','C');
insert into t values('D','E');
insert into t values('C','F');
insert into t values('A','F');
insert into t values('F','A');
insert into t values('H','I');
insert into t values('I','J');
insert into t values('Z','J');
insert into t values('X','Y');
SELECT * FROM T;
COL1 COL2
A B
B C
D E
H I
I J
Z J
F A
C F
X Y
as you can see A, B in row#1 and B, C in row#2 have B common and in row#7 and row#8 F came with A and C , all pairs have some connection, so we have A,B,C,F members in one group
D,E in one group as (D and E appeared once)
H,I,J,Z in one group
and X,Y in one group
so the desired output need to be is given below
char grp
A 1
B 1
C 1
F 1
D 2
E 2
H 3
I 3
J 3
Z 3
X 4
Y 4
please assist in SQL or PLSQL, thanks
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642019 is a reply to message #642013] |
Sun, 30 August 2015 08:55 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Not much of a difference:
with t1 as (
select distinct least(column1,column2) column1,
greatest(column1,column2) column2
from tbl
),
data as (
select connect_by_root column1 root,
column2
from t1
connect by prior column2 = column1
start with column1 not in (select column2 from t1)
)
select nvl(column2,root) col,
dense_rank() over(order by root) grp
from data
group by grouping sets((root),(root,column2))
order by grp,
col
/
C GRP
- ----------
A 1
B 1
C 1
F 1
D 2
E 2
H 3
I 3
J 3
Z 3
X 4
C GRP
- ----------
Y 4
12 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642024 is a reply to message #642020] |
Sun, 30 August 2015 18:16 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I'd suggest changing design since your design stores both upstream and downstream hierarchy in same table. Don't have much time for a better solution, so:
with t1 as (
select distinct least(column1,column2) column1,
greatest(column1,column2) column2
from tbl
),
t2 as (
select connect_by_root column1 root,
column1,
column2,
count(*) over(partition by connect_by_root column1) cnt
from t1
connect by prior column2 = column1
start with column1 not in (select column2 from t1)
),
t3 as (
select distinct greatest(column1,column2) column1,
least(column1,column2) column2
from tbl
),
t4 as (
select connect_by_root column1 root,
column1,
column2,
count(*) over(partition by connect_by_root column1) cnt
from t3
connect by prior column2 = column1
start with column1 not in (select column2 from t3)
),
t5 as (
select least(column1,column2) column1,
greatest(column1,column2) column2,
root,
cnt,
1 flag
from t2
union all
select least(column1,column2) column1,
greatest(column1,column2) column2,
root,
cnt,
2 flag
from t4
),
t6 as (
select case min(flag) keep(dense_rank last order by cnt)
when 1 then column2
else column1
end col,
min(root) keep(dense_rank last order by cnt,flag desc) root
from t5
group by column1,
column2
)
select nvl(col,root) col,
dense_rank() over(order by root) grp
from t6
group by grouping sets((root),(root,col))
order by grp,
col
/
C GRP
- ----------
A 1
B 1
C 1
F 1
D 2
E 2
H 3
I 3
J 3
Z 3
K 4
C GRP
- ----------
L 4
M 4
X 5
Y 5
15 rows selected.
SQL>
SY.
[Updated on: Sun, 30 August 2015 18:23] Report message to a moderator |
|
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642032 is a reply to message #642025] |
Mon, 31 August 2015 11:52 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 data as ( select least(col1,col2) col1, greatest(col1,col2) col2 from t ),
3 all_paths as (
4 select sys_connect_by_path(col1,'/')||'/'||col2||'/' path
5 from data
6 where connect_by_isleaf = 1
7 connect by prior col2 = col1
8 start with col1 not in (select col2 from data)
9 ),
10 grouped as (
11 select a.col1, a.col2, listagg(b.path) within group (order by b.path) path
12 from data a, all_paths b
13 where b.path like '%/'||a.col1||'/%'
14 or b.path like '%/'||a.col2||'/%'
15 group by a.col1, a.col2
16 ),
17 split as (
18 select distinct
19 col1, col2, regexp_substr(path, '[^/]+', 1, column_value) elem
20 from grouped,
21 table(cast(multiset(select level from dual
22 connect by level <= regexp_count(path,'/'))
23 as sys.odciNumberList))
24 where regexp_substr(path, '[^/]+', 1, column_value) is not null
25 ),
26 result as (
27 select col1, col2,
28 dense_rank()
29 over (order by listagg(elem,',') within group (order by elem))
30 grp
31 from split
32 group by col1, col2
33 )
34 select distinct nvl(col1,col2) col, grp
35 from result
36 group by cube(col1, col2, grp)
37 having grouping(grp)=0 and ( grouping(col1)=0 or grouping(col2)=0 )
38 order by 2, 1
39 /
C GRP
- ----------
A 1
B 1
C 1
F 1
D 2
E 2
H 3
I 3
J 3
Z 3
K 4
L 4
M 4
X 5
Y 5
15 rows selected.
|
|
|
|
|
|
| Re: Oracle pair combination with two column values in a table [message #642043 is a reply to message #642037] |
Tue, 01 September 2015 01:14 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
A slightly better solution is:
SQL> with
2 data as ( select least(col1,col2) col1, greatest(col1,col2) col2 from t ),
3 all_paths as (
4 select sys_connect_by_path(col1,'/')||'/'||col2||'/' path
5 from data
6 where connect_by_isleaf = 1
7 connect by prior col2 = col1
8 start with col1 not in (select col2 from data)
9 ),
10 grouped as (
11 select a.col1, a.col2, listagg(b.path) within group (order by b.path) path
12 from data a, all_paths b
13 where b.path like '%/'||a.col1||'/%'
14 or b.path like '%/'||a.col2||'/%'
15 group by a.col1, a.col2
16 ),
17 split as (
18 select distinct
19 col1, col2, regexp_substr(path, '[^/]+', 1, column_value) elem
20 from grouped,
21 table(cast(multiset(select level from dual
22 connect by level <= regexp_count(path,'/'))
23 as sys.odciNumberList))
24 where regexp_substr(path, '[^/]+', 1, column_value) is not null
25 ),
26 result as (
27 select col1, col2,
28 dense_rank()
29 over (order by listagg(elem,',') within group (order by elem))
30 grp
31 from split
32 group by col1, col2
33 )
34 select distinct nvl(col1,col2) col, grp
35 from result
36 group by grouping sets((grp,col1),(grp,col2))
37 order by 2, 1
38 /
C GRP
- ----
A 1
B 1
C 1
F 1
D 2
E 2
H 3
I 3
J 3
Z 3
K 4
L 4
M 4
X 5
Y 5
15 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 15:07:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
