SQL Server PIVOT on key-value table

Question

I have a table that has attributes based on a key-value. Example:

CREATE TABLE ObjectAttributes
(
  int objectId, key nvarchar(64), value nvarchar(512)
)

When I select from this I get:

objectId  key      value
----------------------------
1         Key 1    Value 1
1         Key 2    Value 2

I was wondering if I could use the PIVOT syntax to turn this into:

objectId  Key 1     Key 2
---------------------------
1         Value 1   Value 2

I know all of my tables will have the same keys. (Unfortunately I cannot easily change the table structure. This is what is leading me to attempt using PIVOTS).

The big issue here though is that pivots require an aggregation function to be used. Is there a way to avert this? Am I completely wrong attempting this? Or is there a better solution?

Answer 1

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