1

The starting table.

with t1 as 
(
Select 'obrien-t j' lname_forename_long,11 grid_id_ct ,'grid.416153.4' grid_id,2 name_seq ,1 group_seq UNION ALL
Select 'obrien-t j',1,'grid.1002.3',1,1 UNION ALL
Select 'obrien-terence',2,'grid.1008.9',1,2 UNION ALL
Select 'obrien-terence',4,'grid.416153.4',2,2 UNION ALL
Select 'obrien-terence',1,'grid.484852.7',3,2 UNION ALL
Select 'obrien-terence j',14,'grid.1002.3',1,3 UNION ALL
Select 'obrien-terence j',25,'grid.1008.9',2,3 UNION ALL
Select 'obrien-terence j',3,'grid.1019.9',3,3 UNION ALL
Select 'obrien-terence j',9,'grid.1623.6',4,3 UNION ALL
Select 'obrien-terence j',40,'grid.237081.f',5,3 UNION ALL
Select 'obrien-terence j',1,'grid.267362.4',6,3 UNION ALL
Select 'obrien-terence j',2,'grid.414094.c',7,3 UNION ALL
Select 'obrien-terence j',1,'grid.416060.5',8,3 UNION ALL
Select 'obrien-terence j',36,'grid.416153.4',9,3 UNION ALL
Select 'obrien-terence j',4,'grid.453219.8',10,3 UNION ALL
Select 'obrien-terence j',3,'grid.454055.5',11,3 UNION ALL
Select 'obrien-terence j',6,'grid.474069.8',12,3 UNION ALL
Select 'obrien-terence j',13,'grid.481253.9',13,3 UNION ALL
Select 'obrien-terence john',1,'grid.1002.3',1,4 UNION ALL
Select 'obrien-terence john',1,'grid.1008.9',2,4 UNION ALL
Select 'obrien-terence john',1,'grid.1623.6',3,4 UNION ALL
Select 'obrien-terence john',1,'grid.237081.f',4,4 UNION ALL
Select 'obrien-terence john',2,'grid.416153.4',5,4 UNION ALL
Select 'obrien-terrence',2,'grid.416153.4',1,5 UNION ALL
Select 'obrien-terrence j',1,'grid.416153.4',1,6 UNION ALL
Select 'obrien-terry',1,'grid.137628.9',1,7 UNION ALL
Select 'obrien-terry',2,'grid.237081.f',2,7 UNION ALL
Select 'obrien-terry',1,'grid.267362.4',3,7 UNION ALL
Select 'obrien-timothy',1,'grid.496867.2',1,8 UNION ALL
Select 'obrien-timothy',3,'grid.6142.1',2,8 
)
select * from t1;
lname_forename_long grid_id_ct grid_id name_seq group_seq
obrien-t j 1 grid.1002.3 1 1
obrien-t j 11 grid.416153.4 2 1
obrien-terence 2 grid.1008.9 1 2
obrien-terence 4 grid.416153.4 2 2
obrien-terence 1 grid.484852.7 3 2
obrien-terence j 14 grid.1002.3 1 3
obrien-terence j 25 grid.1008.9 2 3
obrien-terence j 3 grid.1019.9 3 3
obrien-terence j 9 grid.1623.6 4 3
obrien-terence j 40 grid.237081.f 5 3
obrien-terence j 1 grid.267362.4 6 3
obrien-terence j 2 grid.414094.c 7 3
obrien-terence j 1 grid.416060.5 8 3
obrien-terence j 36 grid.416153.4 9 3
obrien-terence j 4 grid.453219.8 10 3
obrien-terence j 3 grid.454055.5 11 3
obrien-terence j 6 grid.474069.8 12 3
obrien-terence j 13 grid.481253.9 13 3
obrien-terence john 1 grid.1002.3 1 4
obrien-terence john 1 grid.1008.9 2 4
obrien-terence john 1 grid.1623.6 3 4
obrien-terence john 1 grid.237081.f 4 4
obrien-terence john 2 grid.416153.4 5 4
obrien-terrence 2 grid.416153.4 1 5
obrien-terrence j 1 grid.416153.4 1 6
obrien-terry 1 grid.137628.9 1 7
obrien-terry 2 grid.237081.f 2 7
obrien-terry 1 grid.267362.4 3 7
obrien-timothy 1 grid.496867.2 1 8
obrien-timothy 3 grid.6142.1 2 8

END RESULT enter image description here The desire result

The colors represent links between the different group_seq. There maybe be more then I have shown. Group_seq 7 (o'brien-terry) can be linked to 3 (o'brien-terence j) if I can link these 2 then I can link o'brien-terry to 1,2,3,4,5,6.

Ideally a boolean flag of true or false would be useful to show all the group_seq that are linked and the ones that are not. In this example o'brien-timothy would be FALSE, all others would be TRUE.

Thanks in advance. It has to be a BQ solution prefereably without creating functions or stored procedures.

The grid_id grid.416153.4 is common to group_se 1,2,3,4,5,6

lname_forename_long grid_id_ct grid_id name_seq group_seq links
obrien-t j 1 grid.1002.3 1 1
obrien-t j 11 grid.416153.4 2 1 1,2,3,4,5,6
obrien-terence 2 grid.1008.9 1 2
obrien-terence 4 grid.416153.4 2 2 1,2,3,4,5,6
obrien-terence 1 grid.484852.7 3 2
obrien-terence j 14 grid.1002.3 1 3 3,7
obrien-terence j 25 grid.1008.9 2 3
obrien-terence j 3 grid.1019.9 3 3
obrien-terence j 9 grid.1623.6 4 3
obrien-terence j 40 grid.237081.f 5 3
obrien-terence j 1 grid.267362.4 6 3
obrien-terence j 2 grid.414094.c 7 3
obrien-terence j 1 grid.416060.5 8 3
obrien-terence j 36 grid.416153.4 9 3 1,2,3,4,5,6
obrien-terence j 4 grid.453219.8 10 3
obrien-terence j 3 grid.454055.5 11 3
obrien-terence j 6 grid.474069.8 12 3
obrien-terence j 13 grid.481253.9 13 3 3,4
obrien-terence john 1 grid.1002.3 1 4
obrien-terence john 1 grid.1008.9 2 4
obrien-terence john 1 grid.1623.6 3 4
obrien-terence john 1 grid.237081.f 4 4 3,4
obrien-terence john 2 grid.416153.4 5 4 1,2,3,4,5,6
obrien-terrence 2 grid.416153.4 1 5 1,2,3,4,5,6
obrien-terrence j 1 grid.416153.4 1 6 1,2,3,4,5,6
obrien-terry 1 grid.137628.9 1 7
obrien-terry 2 grid.237081.f 2 7 3,7
obrien-terry 1 grid.267362.4 3 7
obrien-timothy 1 grid.496867.2 1 8
obrien-timothy 3 grid.6142.1 2 8
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
Denis The Menace
  • 49
  • 7

1 Answers1

1

Consider below approach

select *, if(count(*) over win > 1, string_agg('' || group_seq) over win, '') links
from t1
window win as (partition by grid_id)

if applied to sample data in your question - output is

enter image description here

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • This is brilliant, I had tried so many different approaches, this is my first encounter to see the window function. There is a second part to the question, I did not want to over complicate it, I said I would open a follow on question if the first part was possible. – Denis The Menace Mar 26 '22 at 07:49
  • I accepted the answer yesterday., Green Check Mark – Denis The Menace Mar 26 '22 at 18:55