MySQL self-join missing data

Question

I want to perform a self-join on a 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 Attribute Value 1 a 101b201c302 a 152c25 My target (assuming I know the three possible attributes are a,b,c) is Object ID a bc110203021525

P粉563446579 · Answer

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

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

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