SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select sqlite_version();


sqlite_version()


3.27.2


CREATE TABLE yourtable
(Grp Integer,
 Data Varchar(100)
 );
 INSERT INTO yourtable(Grp,Data) VALUES
 (1,'row 1'),(1,'row 2'),(1,'row 3'),
 (20,'row 1'),(20,'row 3'),
 (10,'row 1'),(10,'row 2'),(10,'row A'),(10,'row 3');
SELECT * FROM yourtable order by Grp ;


Grp
Data


1
row 1

1
row 2

1
row 3

10
row 1

10
row 2

10
row A

10
row 3

20
row 1

20
row 3


SELECT
a.Data,group_concat(case when base.Grp is null then "." else "1" end,'') as Found_In_Group
,group_concat(b.Grp) as Group_Order
FROM
(SELECT Data FROM yourtable Group By Data)a
CROSS JOIN
(SELECT Grp FROM yourtable Group By Grp Order by Grp)b
LEFT JOIN yourtable base
ON  b.Grp=base.Grp
AND a.Data=base.Data
GROUP BY a.Data


Data
Found_In_Group
Group_Order


row 1
111
1,10,20

row 2
11.
1,10,20

row 3
111
1,10,20

row A
.1.
1,10,20


SELECT Data, group_concat("1") AS "Found in group"
FROM yourtable
GROUP BY Data
Order by Grp desc


Data
Found in group


row A
1

row 1
1,1,1

row 2
1,1

row 3
1,1,1

Grp	Data
1	row 1
1	row 2
1	row 3
10	row 1
10	row 2
10	row A
10	row 3
20	row 1
20	row 3

Data	Found_In_Group	Group_Order
row 1	111	1,10,20
row 2	11.	1,10,20
row 3	111	1,10,20
row A	.1.	1,10,20