4

I have a table with rows which have multiple of the same IDs I want to figure out an SQL query which allow me to but the relevant field number and value into a separate column for example; for value 1.3 a new column would be created called first, 1.6 would be last name and so on. I want to attempt to get all of the information into one row so for each where the lead_id value is 79 there would be only one row instead of 9 rows. I'm not sure if this would be at all possible? I have put a preview of the database structure below in an attempt to show what I mean.

ID   lead_id   field_number   Value

1       79         1.3        John
2       79         1.6        Doe
3       79          2         johndoe@example.com
4       79          6         POSTCODE
5       79          3         01332 1234567
6       79          4         DATE OF BIRTH
7       79          7         APPLICATION ID
8       79          9         CITY NAME
9       79          5         RESUME URL
10      80         1.3        Jane
11      80         1.6        Doe
12      80          2         janedoe@example.com
13      80          6         POSTCODE
14      80          3         01332 1234567
15      80          4         DATE OF BIRTH
16      80          7         APPLICATION ID
17      80          9         CITY NAME
18      80          5         RESUME URL

Any help would be greatly appreciated!

Gaz Jones
  • 144
  • 1
  • 14

1 Answers1

2

You can use multiple SELECT queries for this, e.g.:

SELECT t.lead_id,
 (SELECT value FROM table WHERE lead_id = t.lead_id AND field_number = 1.3) as 'first name',
 (SELECT value FROM table WHERE lead_id = t.lead_id AND field_number = 1.6) as 'last name',
 (SELECT value FROM table WHERE lead_id = t.lead_id AND field_number = 2) as 'email',
 (SELECT value FROM table WHERE lead_id = t.lead_id AND field_number = 6) as 'post code',
 (SELECT value FROM table WHERE lead_id = t.lead_id AND field_number = 3) as 'phone',
 (SELECT value FROM table WHERE lead_id = t.lead_id AND field_number = 4) as 'dob'
FROM table t

You can add more SELECTs for more columns.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Thank you I ended up using GROUP_CONCAT(IF(`field_number` = 2, VALUE, NULL)) AS 'Email', Thank you for your help though – Gaz Jones May 19 '17 at 10:10