MySQL self-join missing data

Question

I want to perform a self-join on the table to present the values ​​column-wise. For each object, there are multiple properties (up to a known limit), but not all properties for all objects are stored. I have tried various joins but there are always missing rows and I want null values.

Starting table:

Object ID	Attributes	value
1	one	10
1	b	20
1	c	30
2	one	15
2	c	25

My goal (assuming I know the three possible properties are a,b,c) is

Object ID	one	b	c
1	10	20	30
2	15		25

Answer 1

You can use the following query to achieve it:

SELECT
    ObjectID,  
    SUM(CASE WHEN Attribute = 'a' THEN Value ELSE NULL END) AS a,
    SUM(CASE WHEN Attribute = 'b' THEN Value ELSE NULL END) AS b,
    SUM(CASE WHEN Attribute = 'c' THEN Value ELSE NULL END) AS c
FROM mytable
GROUP BY ObjectID

illustrate:

Using the CASE statement, we select the value of Attribute as a specific value, i.e. "a", "b", etc. So for that particular column, only the value of that particular attribute is selected.

Using SUM we aggregate the values ​​of Value fields. This way, any multi-row value for ObjectID will be aggregated into a single row.

If you are unwilling to use SUM because you may have non-numeric values, you can follow @xQbert's suggestion and use MAX like this:

SELECT
    ObjectID,  
    MAX(CASE WHEN Attribute = 'a' THEN Value ELSE NULL END) AS a,
    MAX(CASE WHEN Attribute = 'b' THEN Value ELSE NULL END) AS b,
    MAX(CASE WHEN Attribute = 'c' THEN Value ELSE NULL END) AS c
FROM mytable
GROUP BY ObjectID