如何用MySQL查询实现WordPress Formmaker表单数据行转列？

Got it, let's tackle this Formmaker data flattening problem step by step. Your initial CASE statement approach is on the right track—we just need to add aggregation to collapse multiple rows per submission into a single flat row, and optionally join with the field mapping table for clarity.

Key Background #

First, let's confirm the tables we're working with (adjust names if your WordPress table prefix isn't wp_):

• Submission table: wp_formmaker_submits (stores raw submit data with one row per form field)
• Field mapping table: wp_formmaker_fields (links element_label/field_id to human-readable field names like "first_name")

Finalized Query (Static Field Mapping) #

If you already know the exact element_label values for each field, use this query with MAX() to aggregate results and eliminate NULLs:

SELECT 
    group_id,
    form_id,
    -- Add a CASE statement for each form field you want to flatten
    MAX(CASE WHEN element_label = 1 THEN element_value END) AS first_name,
    MAX(CASE WHEN element_label = 3 THEN element_value END) AS last_name,
    MAX(CASE WHEN element_label = 5 THEN element_value END) AS email,
    MAX(CASE WHEN element_label = 7 THEN element_value END) AS phone_number,
    MAX(CASE WHEN element_label = 9 THEN element_value END) AS mailing_address,
    date AS submission_timestamp,
    ip AS submitter_ip
FROM 
    wp_formmaker_submits
-- Optional: Filter for a specific form if needed
-- WHERE form_id = 2
GROUP BY 
    group_id, form_id, date, ip
ORDER BY 
    date DESC;

Why MAX()? #

Each group_id (unique submission) has one row per field. MAX() ignores NULL values and pulls the single valid element_value for each field, collapsing all rows for a submission into one flat row. For multi-select fields (like checkboxes), use GROUP_CONCAT() instead to join multiple values:

MAX(CASE WHEN element_label = 11 THEN GROUP_CONCAT(element_value SEPARATOR ', ') END) AS preferred_contact_methods

Query with Field Mapping Table Join #

If you want to use human-readable field names instead of hardcoding element_label values, join with the field mapping table:

SELECT 
    s.group_id,
    s.form_id,
    MAX(CASE WHEN f.field_name = 'first_name' THEN s.element_value END) AS first_name,
    MAX(CASE WHEN f.field_name = 'last_name' THEN s.element_value END) AS last_name,
    MAX(CASE WHEN f.field_name = 'email' THEN s.element_value END) AS email,
    s.date AS submission_timestamp,
    s.ip AS submitter_ip
FROM 
    wp_formmaker_submits s
INNER JOIN 
    wp_formmaker_fields f 
    ON s.element_label = f.field_id 
    AND s.form_id = f.form_id -- Ensure we match fields to the correct form
GROUP BY 
    s.group_id, s.form_id, s.date, s.ip
ORDER BY 
    s.date DESC;

Quick Tips #

• Replace table names with your actual WordPress table prefix (e.g., wp_ might be myblog_ depending on your setup)
• Add as many CASE statements as you have form fields to include in the flat view
• Use WHERE form_id = X to filter submissions for a specific form only

内容的提问来源于stack exchange，提问作者KiaiFighter