Grouping single value from field with multiple values

Basement
1

Things I have tried

Hi, I’ve tried to look up about this specific issue that I have but found none that exactly match my question. Only similar questions are: Querying a single tag from a file with Multiple tags using Dataview - Help - Obsidian Forum and Dataview plugin not recognizing multi-item YAML list - Help - Obsidian Forum

What I’m trying to do

Say I’m writing a fiction and I’m listing characters that appear in each chapter in frontmatter

Chapter-01.md

type: chapter
status: proofread
character: [Tom,  Jane,  John]

Chapter-02.md

type: chapter
status: revise
character: [Tom,  John, Betty]

Chapter-03.md

type: chapter
status: in-progress
character: [John]

In a new file, I want to list each character with what chapters they appears in in a table

I tried like this:

TABLE WITHOUT ID
    character as "Character",
    rows.file.link as "Chapter"
FROM ""
WHERE character
GROUP BY character
SORT character asc

and this is what I got:

Character Chapter
- John Chapter-03
- Tom
- Jane
- John		 Chapter-01
- Tom
- John
- Betty		 Chapter-02

This is what I really want to see:

Character Chapter
Betty Chapter-02
John - Chapter-01
- Chapter-02
- Chapter-03
Tom - Chapter-01
- Chapter-02
Notice the difference in how the values are grouped and listed.

How do I query to get this result? Do I need to jump to dataviewjs in order to do this? Or do I need to change the way I structure the metadata instead?

2

Try this:

TABLE WITHOUT ID
    character as "Character",
    rows.file.link as "Chapter"
FROM ""
WHERE character
FLATTEN character
GROUP BY character
SORT character asc
3

Holy moly it works! Thanks a lot!

I actually tried using flatten before but it didn’t work as I wanted. It didn’t merge the same character into one row. It just broke them apart into their own row. And now I realize why: I’ve always put FLATTEN after GROUP BY. This was it. Putting FLATTEN before GROUP BY as you suggested fixed this completely.