mysql - 根据行中的数据创建多列

Question

我正在尝试构建一个查询，该查询根据下面的 field_number 列创建单独的列。查询应按 Lead_id 分组。 form_id 是比赛(我需要能够根据我需要统计数据的比赛手动更改 form_id)。 field_number 每个数字代表我需要创建的一列。所以 1 = 电子邮件，4 = 名字，5 = 姓氏等等。

任何有关我需要如何执行此操作的帮助都将不胜感激。下面是我需要从中获取数据的表格示例。

    +-----+---------+---------+--------------+---------------------------------+
    | id  | lead_id | form_id | field_number | value                           |
    +-----+---------+---------+--------------+---------------------------------+
    |   1 |       1 |       3 |            1 | michael.smith@gmail.com         |
    |   2 |       1 |       3 |            4 | Michael                         |
    |   3 |       1 |       3 |            5 | Smith                           |
    |   4 |       1 |       3 |            6 | eNewsletter Sign Up Form        |
    |   5 |       2 |       3 |            1 | jack.johnson@gmail.com          |
    |   6 |       2 |       3 |            4 | Jack                            |
    |   7 |       2 |       3 |            5 | Johnson                         |
    |   8 |       2 |       3 |            6 | eNewsletter Sign Up Form        |
    |   9 |       3 |       3 |            1 | bobbie@hotmail.com              |
    |  10 |       3 |       3 |            4 | Bobbie                          |
    |  11 |       3 |       3 |            5 | DeLeon                          |
    |  12 |       3 |       3 |            6 | eNewsletter Sign Up Form        |
    |  13 |       4 |       3 |            1 | noname@gmail.com                |
    |  14 |       4 |       3 |            6 | H&H eNewsletter Sign Up Form    |
    |  15 |       5 |       3 |            1 | judybyers@hotmail.com           |
    |  16 |       5 |       3 |            4 | Judy                            |
    |  17 |       5 |       3 |            5 | Byers                           |
    |  18 |       5 |       3 |            6 | eNewsletter Sign Up Form        |
    |  19 |       6 |       3 |            1 | Andrem@yahoo.com                |
    |  20 |       6 |       3 |            4 | Andre                           |
    |  21 |       6 |       3 |            5 | M                               |
    |  22 |       6 |       3 |            6 | eNewsletter Sign Up Form        |
    |  23 |       7 |       3 |            1 | larryl@gmail.ca                 |
    |  24 |       7 |       3 |            4 | Larry                           |
    |  25 |       7 |       3 |            5 | Landerson                       |
    |  26 |       7 |       3 |            6 | eNewsletter Sign Up Form        |
    |  27 |       8 |       3 |            1 | steph@yahoo.com                 |
    |  28 |       8 |       3 |            4 | Stephanie                       |
    |  29 |       8 |       3 |            6 | eNewsletter Sign Up Form        |

Answer 1

您可以通过聚合来做到这一点:

select lead_id, form_id,
       max(case when field_number = 1 then value end) as email,
       max(case when field_number = 4 then value end) as firstname,
       max(case when field_number = 5 then value end) as lastname
from t
where form_id = @form_id
group by lead_id, form_id;

这假设每个字段对于 lead_id/form_id 组合最多出现一次。