SELECT DISTINCT
p.ID as post_id, 'prefix_list', CONCAT('a:', COUNT(*), ':{', GROUP_CONCAT(CONCAT('i:', f.`delta`, ';s:',LENGTH(f.`field_prefix_list_value`),':"', UPPER(f.`field_prefix_list_value`), '"') SEPARATOR ';'), ';}') AS `prefixes`
FROM `Giwth4_posts` p
INNER JOIN node_field_data n ON (p.post_title = n.title COLLATE utf8mb4_unicode_ci)
INNER JOIN `node__field_prefix_list` f ON f.revision_id = n.vid
WHERE n.status = 1 AND f.deleted = 0 GROUP BY f.`entity_id` ORDER BY f.`delta`
- It starts with a count of how many elements are in a group
- It takes the records, grouped by the common element (in this case, entity_id)
- It concatenates the delta that is avaialble as the array index
- It looks at the length of the value needed
- In my case, I needed upper case strings, so I put in an UPPER() function call
- It separates each one with a semi-colon
- It group concatenates that
- It does the rest of the necessary formatting.
- It orders the elements by their delta inside of the groups to go from lowest to highest index
