MySQL self join with missing data

Question

I'm wanting to perform a self-join on a table to present the values in a column wise manner. For each object there are several attributes (up to a known limit), but not all attributes are stored for all objects. I've tried various joins but I'm always getting missing rows, where I would like to have empty values instead.

Starting Table:

ObjectID	Attribute	Value
1	a	10
1	b	20
1	c	30
2	a	15
2	c	25

What I'm aiming for (given that I know the three possible attributes are a,b,c) is

ObjectID	a	b	c
1	10	20	30
2	15		25

Possible Duplicate of: https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — xQbert, Jun 17 '22 at 13:23

Himanshu · Accepted Answer · 2022-06-17T13:48:43.487

1

You can achieve it using following query:

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

Explaination:

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

Using SUM we are aggregating the value of Value field. So that the value of multiple rows are being aggregated into a single row for any ObjectID.

In case you are not willing to use SUM as you may have non numeric values, you can use MAX as suggested by @xQbert like below:

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

edited Jun 17 '22 at 13:48

answered Jun 17 '22 at 13:13

Himanshu

31,810
31
111
133

1

Additional detail and explanation would help this answer. such as "Conditional aggregation which this uses, can be used to pivot data" This is useful in databases which do not support the "Pivot" function due to being older versions, or the RDBMS just doesn't have that function embedded. – xQbert Jun 17 '22 at 13:27
1

Sure I'll update @xQbert. – Himanshu Jun 17 '22 at 13:31
I'm not wanting to do any sort of aggregation, in fact the values are not necessarily numeric (perhaps bad choices for the example data on my part...). So I guess it is a pivot of sorts but not in a 'summarising data' kind of way, simply transforming it. – beacon_bonanza Jun 17 '22 at 13:32
Change sum to MAX(); but it assumes the ObjectID and attribute are unique on the table. The reason why some aggregation is needed is because if ObjectID and attribute columns are NOT unique in the data, the system wouldn't know how to handle putting two values into a single field. So aggregation is used to ensure the system knows what value to place in the field. If the objectID and attribute columns are unique, then MAX or MIN can be used and achieve teh desired results – xQbert Jun 17 '22 at 13:33
However, if they are unique and numeric, sum serves the same function as it isn't actually summing anything... – xQbert Jun 17 '22 at 13:39
2

That works exactly as expected when using max, thanks xQbert and @Himanshu for you input. – beacon_bonanza Jun 17 '22 at 13:39
1

@Himanshu awsome! Much more useful to others who come across this question. Thanks! – xQbert Jun 17 '22 at 13:46

MySQL self join with missing data

1 Answers1