It's not done using dynamic SQL tables.
The key insight is to note that FIELDS table actually defines the name, ordering, and config for each of the fields of a given note type, and that the actual notes data is stored in the Notes table in the DATA column, most likely as JSON.
Dynamic tables like you're describing are covered in the Metadata Tribbles chapter of the book SQL Antipatterns.
https://pragprog.com/titles/bksqla/sql-antipatterns/