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?

CodePudding user response：

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

will give you a CSV list of groups.

CodePudding user response：

To find the missing values, first thing is to prepare a dataset which have all possible combination. You can achieve that using CROSS JOIN.

Once you have that DataSet, compare it with the actual DataSet.

Considering the Order by is done in the Grp column, you can achieve it using below.

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

Note: Considered . instead of blank for better visibility to represent missing Group.

Demo: Try here