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:

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

CodePudding user response：

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