I have the following two tables. Each Object can have 0-4 corresponding Attributes, which are stored in the Attributes table.

Objects

Attributes

Now I want the following result, where for every possible Attribute a column is created which contains either the corresponing Attribute or NULL.

AttributedObjects

I came up with two queries, which produce the wanted result. The database I'm forced to work with doesn't support the PIVOT clause. Under these circumstances, which of the following two is more efficient for the wanted result?

Query based on SELECT

SELECT ID, Title,
    (SELECT AttributeValue FROM Attributes WHERE AttributeName = "K1" AND ObjectID = Objects.ID) AS K1,
    (SELECT AttributeValue FROM Attributes WHERE AttributeName = "K2" AND ObjectID = Objects.ID) AS K2,
    (SELECT AttributeValue FROM Attributes WHERE AttributeName = "K3" AND ObjectID = Objects.ID) AS K3,
    (SELECT AttributeValue FROM Attributes WHERE AttributeName = "K4" AND ObjectID = Objects.ID) AS K4
FROM Objects;

Query based on JOIN

SELECT Objects.ID, Objects.Title, Q1.K1, Q2.K2, Q3.K3, Q4.K4
FROM (((Objects 
    LEFT JOIN (SELECT ObjectID, AttributeValue AS K1 FROM Attributes WHERE AttributeName = "K1")
        AS Q1 ON Objects.ID = Q1.ObjectID)
    LEFT JOIN (SELECT ObjectID, AttributeValue AS K2 FROM Attributes WHERE AttributeName = "K2")
        AS Q2 ON Objects.ID = Q2.ObjectID)
    LEFT JOIN (SELECT ObjectID, AttributeValue AS K3 FROM Attributes WHERE AttributeName = "K3")
        AS Q3 ON Objects.ID = Q3.ObjectID)
    LEFT JOIN (SELECT ObjectID, AttributeValue AS K4 FROM Attributes WHERE AttributeName = "K4")
        AS Q4 ON Objects.ID = Q4.ObjectID;

CodePudding user response：

Use Pivot with Join

SELECT ID,Title, 
  K1,K2,K3,K4  
FROM  
(
  SELECT o.ID,Title,AttributeName, AttributeValue   
  FROM Objects o
  JOIN Attributes a ON o.ID = a.ObjectID
) AS SourceTable  
PIVOT  
(  
  AVG(AttributeValue)  
  FOR AttributeName IN (K1,K2,K3,K4)  
) AS PivotTable;  

Demo in db<>fiddle

CodePudding user response：

Use conditional aggregation as follows:

select o.id, o.title,
    Max(case when AttributeName='K1' then AttributeValue end) K1,
    Max(case when AttributeName='K2' then AttributeValue end) K2,
    Max(case when AttributeName='K3' then AttributeValue end) K3,
    Max(case when AttributeName='K4' then AttributeValue end) K4
from objects o
join attributes a on a.ObjectID = o.id
group by o.Id, o.title

CodePudding user response：

I propose one more alternative which seems more efficient using IF or CASE and GROUP BY, so you don't have to pivot anything. Because I don't know what you do with those values, I used SUM. See the following:

SELECT Title,
       SUM(IF(AttributeName = 'K1', AttributeValue, 0)) AS K1,
       SUM(IF(AttributeName = 'K2', AttributeValue, 0)) AS K2,
       SUM(IF(AttributeName = 'K3', AttributeValue, 0)) AS K3,
       SUM(IF(AttributeName = 'K4', AttributeValue, 0)) AS K4
FROM AttributedObjects AS AO
INNER JOIN Attributes AS A ON AO.ObjectID = O.ID
GROUP BY Title