Can I generate a map that shows a particular row was in a particular group in SQLite?

Question

Say I have the following data:

+--------+-------+
| Group  | Data  |
+--------+-------+
| 1      | row 1 |
| 1      | row 2 |
| 1      | row 3 |
| 20     | row 1 |
| 20     | row 3 |
| 10     | row 1 |
| 10     | row A |
| 10     | row 2 |
| 10     | row 3 |
+--------+-------+

Is it possible to draw a map that shows which groups have which rows? Groups may not be contagious, so they can be placed into a separate table and use the row index for the string index instead. Something like this:

+-------+
| Group |
+-------+
| 1     |
| 20    |
| 10    |
+-------+

+-------+----------------+
| Data  | Found in group |
+-------+----------------+
| row 1 | 111            |
| row A |   1            |
| row 2 | 1 1            |
| row 3 | 111            |
+-------+----------------+

Where the first character represents Group 1, the 2nd is Group 20 and the 3rd is Group 10.

Ordering of the Group rows isn't critical so long as I can reference which row goes with which character.

I only ask this because I saw this crazy example in the documentation generating a fractal, but I can't quite get my head around it.

Is this doable?

Answer 1

SELECT Data, group_concat("Group") AS "Found in group"
FROM yourtable
GROUP BY Data

will give you a CSV list of groups.