Creating Multiple Columns based on the data in a row(s)

Question

I am trying to construct a query that creates separate columns based on the field_number column below. The query should be grouped by the lead_id. The form_id is the contest (I would need to be able to change the form_id manually based on the contest I need stats for). The field_number each number represents a column I would need to create. So 1 = email, 4 = First Name, 5 = Last name etc. etc.

Any help on how I need to do this is appreciated. There is an example below of the table I need to grab the data from.

    +-----+---------+---------+--------------+---------------------------------+
    | id  | lead_id | form_id | field_number | value                           |
    +-----+---------+---------+--------------+---------------------------------+
    |   1 |       1 |       3 |            1 | [email protected]         |
    |   2 |       1 |       3 |            4 | Michael                         |
    |   3 |       1 |       3 |            5 | Smith                           |
    |   4 |       1 |       3 |            6 | eNewsletter Sign Up Form        |
    |   5 |       2 |       3 |            1 | [email protected]          |
    |   6 |       2 |       3 |            4 | Jack                            |
    |   7 |       2 |       3 |            5 | Johnson                         |
    |   8 |       2 |       3 |            6 | eNewsletter Sign Up Form        |
    |   9 |       3 |       3 |            1 | [email protected]              |
    |  10 |       3 |       3 |            4 | Bobbie                          |
    |  11 |       3 |       3 |            5 | DeLeon                          |
    |  12 |       3 |       3 |            6 | eNewsletter Sign Up Form        |
    |  13 |       4 |       3 |            1 | [email protected]                |
    |  14 |       4 |       3 |            6 | H&H eNewsletter Sign Up Form    |
    |  15 |       5 |       3 |            1 | [email protected]           |
    |  16 |       5 |       3 |            4 | Judy                            |
    |  17 |       5 |       3 |            5 | Byers                           |
    |  18 |       5 |       3 |            6 | eNewsletter Sign Up Form        |
    |  19 |       6 |       3 |            1 | [email protected]                |
    |  20 |       6 |       3 |            4 | Andre                           |
    |  21 |       6 |       3 |            5 | M                               |
    |  22 |       6 |       3 |            6 | eNewsletter Sign Up Form        |
    |  23 |       7 |       3 |            1 | [email protected]                 |
    |  24 |       7 |       3 |            4 | Larry                           |
    |  25 |       7 |       3 |            5 | Landerson                       |
    |  26 |       7 |       3 |            6 | eNewsletter Sign Up Form        |
    |  27 |       8 |       3 |            1 | [email protected]                 |
    |  28 |       8 |       3 |            4 | Stephanie                       |
    |  29 |       8 |       3 |            6 | eNewsletter Sign Up Form        |

Gordon Linoff · Accepted Answer · 2017-01-03 22:31:04Z

1

You can do this with aggregation:

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;

This assumes that each field appears at most one time for a lead_id/form_id combination.

answered Jan 3, 2017 at 22:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

leeyuiwah · Accepted Answer · 2017-01-03 22:22:39Z

I don't understand your requirement about form_id, but for getting the value in rows pivoted into column, the query would look like this (I am doing only three as an example, you can add other value by keep adding the inner join clauses)

select
    t1.lead_id
    , t1.value      as email
    , t4.value      as firstName
    , t5.value      as lastName
from
    (
        select
            lead_id
            , value
        from
            data_table
        where 
            field_number = 1 -- email address
    ) as t1
    , inner join
    (
        select
            lead_id
            , value
        from
            data_table
        where 
            field_number = 4 -- first name
    ) as t4
        on t1.lead_id = t4.lead_id 
    , inner join
    (
        select
            lead_id
            , value
        from
            data_table
        where 
            field_number = 5 -- last name
    ) as t5
    on t1.lead_id = t5.lead_id

Collectives™ on Stack Overflow

Creating Multiple Columns based on the data in a row(s)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related