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

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!

CodePudding user response：

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

CodePudding user response：

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';

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;