Structure
| ID | Attribute_id | Value |
|---|---|---|
| 1 | attribute_1 | X_firstname |
| 1 | attribute_2 | X_Lastname |
| 1 | attribute_3 | X_Occupation |
| 2 | attribute_1 | Y_Firstname |
| 2 | attribute_2 | Y_Occupation |
Result expected
| ID | First Name | Last Name | Occupation |
|---|---|---|---|
| 1 | X_Firstname | X_Lastname | X_Occupation |
| 2 | Y_Firstname | null | Y_Occupation |
How to achieve this? so one ID can have 200 to 300 attributes and exists in one column but we have to fetch specific attributes and show in multiple columns. Any help please
CodePudding user response：
You need to use left outer join for this. The query will look like this:
CREATE TEMP TABLE tmp as
SELECT ID
FROM table
GROUP BY ID;
SELECT tmp.id, t1.Value as First_Name, t2.value as Last_name
FROM tmp
LEFT OUTER JOIN table t1 on tmp.id = t1.id and t1.Attribute_id = 'attribute_1'
LEFT OUTER JOIN table t2 on tmp.id = t2.id and t2.Attribute_id = 'attribute_2'
;
The first query will get all ids, then we will join all attributes to these ids.