SQL Server PIVOT on key-value table

No, you can't avoid the aggregate. SQL Server needs some way of combining the many possible rows into one value. You happen to have one value but the PIVOT functionality is made with many rows in mind.

SELECT objectId, [Key 1], [Key 2]
FROM
(SELECT objectId, [key], value FROM ObjectAttributes) AS source
PIVOT
(
 MIN(value)
 FOR [key] IN ([Key 1], [Key 2])
) as pvt

A pivot will be no faster then repeated self joins for a fixed column output.

SELECT
   T1.objectID, T1.Value AS Key1, T2.Value AS Key2
FROM
   ObjectAttributes T1
   JOIN
   ObjectAttributes T2 ON T1.objectID = T2.objectID
WHERE
   T1.key = 'Key 1'
   AND
   T2.key = 'Key 2'

If you want to use PIVOT, then just use MAX. Because you have one row per object/key it's trivial anyway and is there to satisfy the PIVOT requirement.

If you want to PIVOT an unknown number of rows into columns, then it's dynamic SQL (as per SQL Server 2000 solutions) or do it in the client code.

If each object has a fixed number of attributes then I would consider having a 2nd table with real columns maintained by a trigger. Clumsy, but makes life easier for reading

I am adding this answer since this thread is one of the first that appears on Google for this question.

The simplest solution that I have found is to use the combination of max with case, like this:

-- Pivot the data with a handwritten T-SQL statement.
-- Make sure you have an index defined on the grouping column.
SELECT
    RecordID,
    -- Spreading and aggregation phase
    MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName',
    MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName',
    MAX(CASE WHEN Element = 'City' THEN Value END) AS 'City',
    MAX(CASE WHEN Element = 'Country' THEN Value END) AS 'Country'
FROM EAVTable
GROUP BY RecordID -- Grouping phase

Ref: https://www.sqlpassion.at/archive/2014/08/25/the-dangerous-beauty-of-the-pivot-operator-in-sql-server/

This solution also works pretty much everyone, and you don't need to a lot of join if you need to pivot many columns