MySQL Merge multiple rows with one ID into one row
I have a table with rows that have several of the same ids. I want to figure out a SQL query that allows me, apart from the corresponding field number and value, into a separate column, for example; for value 1.3 a new column named first will be created, 1.6 will be last name, etc. I want to try and get all the information in one line, so for each where the lead_id value is 79, there will be only one line instead of 9 lines. I'm not sure if this is even possible? I've 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!
source to share
Several queries can be used for this SELECT
, for example:
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 SELECT
for more columns.
source to share