SQL to Consolidate table with lots of custom user fields against each user ID

user_7478329 0
Viewed 26

I have a database table like so:

user_id field_id field_value
1 first_name Jim
1 last_name Bob
1 postcode 12345
2 first_name Joe
2 last_name Blogs
2 postcode 67890

I need to convert it to the format below, converting the custom fields to columns and deduplicating the user ids.

user_id first_name last_name postcode
1 Jim Bob 12345
2 Joe Blogs 67890

How can I do this in SQL and how would you describe technically what it is I'm trying to do? I can't think of the right terminology to search for a solution to this.

I have over 20 custom fields, which may change over time so a scalable solution would be nice, but I don't mind writing a manual query for for my current setup just to get it working.

Thanks!

excel mysql sql database
2 Answers

Use:

select user_id,
       max(case when field_id = 'first_name' then field_value end) as first_name,
       max(case when field_id = 'last_name' then field_value end) as last_name,
       max(case when field_id = 'postcode' then field_value end) as postcode
from test_tbl
group by user_id;

Demo

user_164619520 
SELECT user_id, 
       t1.field_value first_name, 
       t2.field_value last_name, 
       t3.field_value postcode
FROM tablename t1
JOIN tablename t2 USING (user_id)
JOIN tablename t3 USING (user_id)
WHERE t1.field_id = 'first_name'
  AND t2.field_id = 'last_name'
  AND t3.field_id = 'postcode';

or

SELECT user_id, 
       MAX(CASE WHEN field_id = 'first_name' THEN field_value END) first_name, 
       MAX(CASE WHEN field_id = 'last_name' THEN field_value END) last_name, 
       MAX(CASE WHEN field_id = 'postcode' THEN field_value END) postcode
FROM tablename
GROUP BY user_id;
user_101387340
Related